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']]