def sheet_to_pandas(spreadsheetId, sheetName='', sheetRange='', index=''):
'''
PARAMETERS:
service - Api service
spreadsheetId - Id of the desired document
sheetName - Name of the desired page 'Hoja1' (optional) (by default: first page)
sheetRange - Range of the desired info 'A1:C6' (optional) (by default: WHOLE PAGE)
index - column you want to be the index of the resulting dataframe (optional) (by default: none of the columns is set as index)
'''
service = auth.get_service("sheets")
if (sheetRange != ''):
sheetRange = '!'+sheetRange
newresult = service.spreadsheets().values().get(
spreadsheetId=spreadsheetId,
valueRenderOption='FORMATTED_VALUE',
range=sheetName+sheetRange
).execute()
headers = newresult['values'].pop(0)
if (index == ''):
return pd.DataFrame(newresult['values'], columns=headers)
else:
return pd.DataFrame(newresult['values'], columns=headers).set_index(index, drop=False)
def execute_batch_update(requests, presentation_id):
body = {'requests': requests}
slides_service = auth.get_service("slides")
response = slides_service.presentations().batchUpdate(
presentationId=presentation_id, body=body).execute()
return response
def upload_image_to_drive(image_name: str,
image_file_path: str,
folder_destination_id='None'):
service = gs.get_service("drive")
file = service.files().create(body={
'name': image_name,
'mimeType': 'image/png'
},
media_body=image_file_path).execute()
file_id = file.get('id')
service.permissions().create(fileId=file_id,
body={
"role": "reader",
"type": "anyone",
"withLink": True
}).execute()
image_url = 'https://drive.google.com/uc?id={file_id}'.format(
file_id=file_id)
# Copy the image to destination if passed
if not folder_destination_id:
move_file(file_id=file_id, folder_destination_id=folder_destination_id)
return {'image_url': image_url, 'file_id': file_id}
def pandas_to_sheet(sheetId, pageName, df):
'''
Uploads a pandas.dataframe to the desired page of a google sheets sheet.
SERVICE ACCOUNT MUST HAVE PERMISIONS TO WRITE IN THE SHEET.
Aditionally, pass a list with the new names of the columns.
Data must be utf-8 encoded to avoid errors.
'''
service = gs.get_service("sheets")
df.fillna(value=0, inplace=True)
columnsList = df.columns.tolist()
valuesList = df.values.tolist()
try:
data = [
{
'range': pageName + '!A1',
'values': [columnsList] + valuesList
},
]
body = {'valueInputOption': 'USER_ENTERED', 'data': data}
result = service.spreadsheets().values().batchUpdate(
spreadsheetId=sheetId, body=body).execute()
return 'True'
except e as Exception:
print(e)
def execute_batch_update(requests, presentation_id, additional_apis=[]):
body = {'requests': requests}
service = auth.get_service("slides", additional_apis=additional_apis)
response = service.presentations().batchUpdate(
presentationId=presentation_id, body=body).execute()
return response
def clear_sheet(spreadsheetId, sheetName, sheetRange=''):
service = auth.get_service("sheets")
if (sheetRange != ''):
sheetRange = '!'+sheetRange
newresult = service.spreadsheets().values().clear(
spreadsheetId=spreadsheetId,
range=sheetName+sheetRange
).execute()
def download_file(file_id,
destination_path='test.pdf',
mime_type='application/pdf'):
service = gs.get_service("drive")
data = service.files().export(fileId=file_id, mimeType=mime_type).execute()
f = open(destination_path, 'wb')
f.write(data)
f.close()
return
def list_folders_in_folder(parent_folder, team_drive_id):
service = gs.get_service("drive")
response = service.files().list(
teamDriveId=team_drive_id,
includeTeamDriveItems=True,
corpora='teamDrive',
supportsTeamDrives=True,
q="mimeType='application/vnd.google-apps.folder' \
and parents='{parent_folder}'".format(
parent_folder=parent_folder)).execute()
return response['files']
def create_folder(name, parent_folder: list = list()):
service = gs.get_service("drive")
file_metadata = {
'name': name,
'mimeType': 'application/vnd.google-apps.folder',
'parents': parent_folder,
}
response = service.files().create(body=file_metadata,
fields='id',
supportsTeamDrives=True).execute()
return response
def delete_object(presentation_id: str, object_id: str = None):
requests = [
{
'deleteObject': {
'objectId': object_id
}
},
]
body = {'requests': requests}
service = auth.get_service("slides")
response = service.presentations().batchUpdate(
presentationId=presentation_id, body=body).execute()
return response
def delete_presentation_notes(presentation_id):
slides_service = auth.get_service("slides")
_slides = get_presentation_slides(presentation_id)
elements_to_delete = []
for _slide in _slides:
if 'notesPage' in _slide['slideProperties']:
for element in _slide['slideProperties']['notesPage'][
'pageElements']:
if 'textRun' in str(element):
elements_to_delete.append(element['objectId'])
batch_delete_text(presentation_id, elements_to_delete)
def copy_file(file_from_id, new_file_name=''):
"""
By passing an old file id, creates a copy and returns the id of the file copy
"""
print('Copying file {} with name {}'.format(file_from_id, new_file_name))
body = {'name': new_file_name}
service = gs.get_service("drive")
drive_response = service.files().copy(
fileId=file_from_id,
body=body,
supportsTeamDrives=True,
).execute()
new_file_id = drive_response.get('id')
return new_file_id
def move_file(file_id, folder_destination_id):
print('Moving file id {} to folder with id{}'.format(
file_id, folder_destination_id))
service = gs.get_service("drive")
file = service.files().get(fileId=file_id,
fields='parents',
supportsTeamDrives=True).execute()
previous_parents = ",".join(file.get('parents'))
response = service.files().update(fileId=file_id,
addParents=folder_destination_id,
removeParents=previous_parents,
supportsTeamDrives=True,
fields='id, parents').execute()
return response
def change_sheet_title(newFileName, fileId):
service = gs.get_service("sheets")
body = {
"requests": [{
"updateSpreadsheetProperties": {
"properties": {
"title": newFileName
},
"fields": "title"
}
}]
}
service.spreadsheets().batchUpdate(spreadsheetId=fileId,
body=body).execute()
return
def get_folder_id_by_name(name, team_drive_id):
service = gs.get_service("drive")
response = service.files().list(
teamDriveId=team_drive_id,
includeTeamDriveItems=True,
corpora='teamDrive',
supportsTeamDrives=True,
q="mimeType='application/vnd.google-apps.folder' \
and name='{name}'".format(
name=name)).execute()
if len(response['files']) > 1:
print('Warning: There\'s more than 1 folder with name {}'.format(name))
elif len(response['files']) == 0:
raise ('TODO: Create folder {}'.format(name))
return response['files'][0]['id']
def text_replace(old: str, new: str, presentation_id: str, pages=None):
if pages is None:
pages = []
service = auth.get_service("slides")
service.presentations().batchUpdate(
body={
"requests": [{
"replaceAllText": {
"containsText": {
"text": '{{' + old + '}}'
},
"replaceText": new,
"pageObjectIds": pages,
}
}]
},
presentationId=presentation_id).execute()
def transform_object(presentation_id: str,
object_id: str,
transform,
apply_mode='ABSOLUTE'):
requests = [
{
'updatePageElementTransform': {
'objectId': object_id,
'transform': transform,
'applyMode': apply_mode
}
},
]
body = {'requests': requests}
service = auth.get_service("slides")
response = service.presentations().batchUpdate(
presentationId=presentation_id, body=body).execute()
return response
def reindex_slides(presentation_id: str, slide_ids: list, new_index=-1):
if new_index < 0:
number = len(get_presentation_slides(presentation_id)) + new_index + 1
else:
number = new_index
requests = [
{
"updateSlidesPosition": {
"slideObjectIds": slide_ids,
"insertionIndex": number,
},
},
]
body = {'requests': requests}
service = auth.get_service("slides")
response = service.presentations().batchUpdate(
presentationId=presentation_id, body=body).execute()
return response
def get_presentation_info(presentation_id): # Class ??
service = auth.get_service("slides")
presentation = service.presentations().get(
presentationId=presentation_id).execute()
return presentation
def get_sheet_info(sheetId):
service = auth.get_service("sheets")
response = service.spreadsheets().get(spreadsheetId=sheetId).execute()
return response
def get_file_name(file_id):
service = gs.get_service("drive")
response = service.files().get(fileId=file_id, fields='name').execute()
return response
def delete_file(file_id):
service = gs.get_service("drive")
response = service.files().delete(fileId=file_id).execute()
return response
def create_presentation(name):
service = auth.get_service("slides")
presentation = service.presentations().create(body={
"title": name
}).execute()
return presentation['presentationId']
def __init__(self):
self.drive_service = gs.get_service("drive")
def get_sheet_names(sheetId):
service = gs.get_service("sheets")
response = service.spreadsheets().get(spreadsheetId=sheetId).execute()
return [a['properties']['title'] for a in response['sheets']]
