def build_vfr_duration_sheet(auth: ComboAuth, spreadsheet_id: int, service: Resource): cli_library.echo('retrieving all stories') # get stories ordered by duration jsonr = tasks.search_for_stories_ord_duration(auth) cli_library.echo('story retrieval complete') # create arrays of data to put into the sheet # here's a header row data = [{ 'range': 'A1:D1', 'values': [['Cost of Delay', 'Issue key', 'Summary', 'Duration']] }] row_idx = 2 # create an array of requests that will hold "updateCells" requests # these are rows that will have the background color changed update_cells_requests = [] update_cells = {'requests': update_cells_requests} # create a var to hold the "previous" duration value prev_duration = 0 cli_library.echo('generating data for duration sheet cells') # iterate through the json results and build up all the other rows of data for issue in jsonr['issues']: # if idx > 2, then check to see if current duration val is different from "previous" val # if so, then add an updateCells request and increment counter, then append row of data # by default we always append a row of data if row_idx == 2: prev_duration = issue['fields']['customfield_18400'] if row_idx > 2 and issue['fields'][ 'customfield_18400'] != prev_duration: update_cells_requests.append( build_color_update_details(0, row_idx - 1, row_idx, 0, 5)) prev_duration = issue['fields']['customfield_18400'] row_idx += 1 data.append(build_vfr_details('', row_idx, row_idx, issue)) row_idx += 1 update_cells_requests.append(build_auto_resize_details(0, 0, 3)) response = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=update_cells).execute() cli_library.echo('duration cell formats updated') body = {'valueInputOption': 'USER_ENTERED', 'data': data} result = service.spreadsheets().values().batchUpdate( spreadsheetId=spreadsheet_id, body=body).execute() cli_library.echo('duration cell data updated')
def batch_update( spreadsheet_id: ID, *, requests: List[BatchRequest], sheets_service: discovery.Resource = None, ) -> None: """ Perform an operation on the spreadsheet. """ if sheets_service is None: sheets_service = sheets_api.build_service() body = {"requests": requests} sheets_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
def create_new_spreadsheet(service: Resource, title: str): """Function to create a new spreadsheet given a client connection to the GSuite API, and a title for the new sheet. # TODO: add functionality to specify "filepath"? Args: service (googleapiclient.discovery.Resource): Connection to the Google API Sheets resource. title (str): Name for the new spreadsheet document Returns: id (str): the id of the spreadsheet """ if not isinstance(title, str): raise TypeError( "The name of the spreadsheet must be given as a string.") request = {"properties": {"title": title}} spreadsheet = service.spreadsheets().create( body=request, fields="spreadsheetId").execute() id = spreadsheet.get("spreadsheetId") return Spreadsheet(service=service, id=id)
def delete_sheet(service: Resource, args: dict) -> CommandResults: # pragma: no cover ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults): Command result with only readable output Action: deletes a sheet from a spreadsheet ''' spreadsheet_id = args.get('spreadsheet_id') request_to_update = { "requests": [{ "deleteSheet": { "sheetId": args.get('sheet_id'), } }], "includeSpreadsheetInResponse": args.get('echo_spreadsheet'), } request_to_update = remove_empty_elements(request_to_update) response = service.spreadsheets().batchUpdate( spreadsheetId=spreadsheet_id, body=request_to_update).execute() readable_comment = 'deleted the sheet' results = prepare_result(response, args, readable_comment) return results
def duplicate_sheet(service: Resource, args: dict) -> CommandResults: ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults) : the command result containing the duplicate spreadsheet sheet update api call Action : duplicates a sheet within a spreadsheet ''' spreadsheet_id = args.get('spreadsheet_id') request_to_update = { "requests": [{ "duplicateSheet": { "sourceSheetId": args.get('source_sheet_id'), "insertSheetIndex": args.get('new_sheet_index'), "newSheetName": args.get('new_sheet_name'), } }], "includeSpreadsheetInResponse": args.get('echo_spreadsheet'), } request_to_update = remove_empty_elements(request_to_update) response = service.spreadsheets().batchUpdate( spreadsheetId=spreadsheet_id, body=request_to_update).execute() readable_comment = 'duplicated the sheet' results = prepare_result(response, args, readable_comment) return results
def copy_to_sheet(service: Resource, args: dict) -> CommandResults: ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults): Command result with only readable output Action: Copies a spreadsheet sheet from one spreadsheet to another ''' spreadsheet_id_to_copy = args.get('source_spreadsheet_id') sheet_id_to_copy = args.get('source_sheet_id') copy_sheet_to_another_spreadsheet_request_body = { 'destination_spreadsheet_id': args.get('destination_spreadsheet_id'), } request = service.spreadsheets().sheets().copyTo( spreadsheetId=spreadsheet_id_to_copy, sheetId=sheet_id_to_copy, body=copy_sheet_to_another_spreadsheet_request_body) request.execute( ) # we don't save the response because there is no need for output or HR results = CommandResults( readable_output="### Successfully copied the sheet") return results
def range_delete_sheet(service: Resource, args: dict) -> CommandResults: # pragma: no cover ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults): Command result with only readable output Action: Deletes a specified range from a sheet in a specified spreadsheet ''' spreadsheet_id = args.get('spreadsheet_id') request_to_update = { "requests": [{ "deleteRange": { "range": { "sheetId": args.get('sheet_id'), "startRowIndex": args.get('start_row_index'), "endRowIndex": args.get('end_row_index'), "startColumnIndex": args.get('start_column_index'), "endColumnIndex": args.get('end_column_index'), }, "shiftDimension": args.get('shift_dimension'), } }], "includeSpreadsheetInResponse": args.get('echo_spreadsheet'), } request_to_update = remove_empty_elements(request_to_update) response = service.spreadsheets().batchUpdate( spreadsheetId=spreadsheet_id, body=request_to_update).execute() readable_comment = 'deleted range' results = prepare_result(response, args, readable_comment) return results
def value_update_sheets(service: Resource, args: dict) -> CommandResults: ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults): Command result with only readable output Action: Updates values in the spreadsheets sheet ''' spreadsheet_id = args.get('spreadsheet_id') input_option = args.get('input_option') ranges = args.get('range') value_range_body = { "majorDimension": args.get('major_dimension'), "values": handle_values_input(str(args.get('values'))), } request = service.spreadsheets().values().update( spreadsheetId=spreadsheet_id, range=ranges, valueInputOption=input_option, body=value_range_body) request.execute() markdown = '### Successfully updated sheet values' return CommandResults(readable_output=markdown)
def value_append_sheets(service: Resource, args: dict) -> CommandResults: # pragma: no cover ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults): Command result with only readable output Action: Appends values to a spreadsheets sheet ''' spreadsheet_id = args.get('spreadsheet_id') range_ = args.get('range') input_option = args.get('input_option') insert_option = args.get('insert_option') value_range_body = { "majorDimension": args.get('major_dimension'), "values": handle_values_input(str(args.get("values"))), } request = service.spreadsheets().values().append( spreadsheetId=spreadsheet_id, range=range_, valueInputOption=input_option, insertDataOption=insert_option, body=value_range_body) request.execute() markdown = '### Successfully appended values to the sheet' return CommandResults(readable_output=markdown)
def default_ranges_if_not_specified(spreadsheet: str, ranges: str, include_grid_data: bool, service: Resource) -> \ Optional[str]: """ Args: ranges: (str) A Google A1 notation ranges include_grid_data: (bool) argument specified by the user spreadsheet (str): The spreadsheet ID service (Google Resource): google-api discovery resource (google api client) Returns: (str) - A1 notation: The original ranges if specified or a default range if not Action: if include_grid_data is specified without a range, instead of overflowing the server with all the spreadsheet data we will take only a default range 500 rows 120 columns """ if not ranges and include_grid_data: response = service.spreadsheets().get( spreadsheetId=spreadsheet).execute() first_sheet_title = response.get("sheets", [])[0].get("properties", {}).get("title") default_range = first_sheet_title + "!A1:T500" return default_range elif ranges == 'None': return None else: return ranges
def get_spreadsheet(service: Resource, args: dict) -> CommandResults: ''' Args: service - google-api discovery resource (google api client) args - demisto.args() for the api call output : command result action : gets a single or multiple spreadsheets ''' spread_sheets_ids = argToList(args.get('spreadsheet_id')) include_grid_data = argToBoolean(args.get('include_grid_data', False)) ranges = args.get('ranges') markdown = "" if not spread_sheets_ids: raise DemistoException('No spreadsheet ID given') if len(spread_sheets_ids) > 1: for spreadsheet in spread_sheets_ids: response = service.spreadsheets().get( spreadsheetId=spreadsheet).execute() markdown += markdown_single_get(response) markdown += '---\n' markdown = '### Success\n\n' + markdown return CommandResults(readable_output=markdown) else: ranges = default_ranges_if_not_specified(spread_sheets_ids[0], str(ranges), include_grid_data, service) request = service.spreadsheets().get( spreadsheetId=spread_sheets_ids[0], ranges=ranges, includeGridData=include_grid_data) response = request.execute() output_response = context_single_get_parse(response, include_grid_data) if include_grid_data: markdown = markdown_single_get_include_grid_data(output_response) else: markdown = markdown_single_get(response) markdown = '### Success\n' + markdown results = CommandResults(readable_output=markdown, outputs_prefix='GoogleSheets.Spreadsheet', outputs_key_field='spreadsheetId', outputs=output_response) return results
def create_spreadsheet(service: Resource, args: dict) -> CommandResults: ''' Args: service (Google Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults) command result ready for the server Action : creates a new spreadsheet ''' rgb_format = argToList(args.get('cell_format_backgroundColor')) rgb_format = [1, 1, 1, 1] if not rgb_format else rgb_format spreadsheet = { "properties": { "title": args.get('title'), "locale": args.get('locale', "en"), "defaultFormat": { "numberFormat": { "type": args.get('cell_form_at_type', 'TEXT'), }, "backgroundColor": { "red": rgb_format[0], "green": rgb_format[1], "blue": rgb_format[2], "alpha": rgb_format[3] }, "textFormat": { "fontFamily": args.get('cell_format_textformat_family', 'ariel'), "fontSize": args.get('cell_format_textformat_font_size', 11), }, "textDirection": args.get('cell_format_text_direction', 'LEFT_TO_RIGHT'), } }, "sheets": [{ "properties": { "title": args.get('sheet_title'), "sheetType": args.get('sheet_type', "GRID"), } }] } spreadsheet = remove_empty_elements(spreadsheet) response = service.spreadsheets().create(body=spreadsheet).execute() human_readable = { 'spreadsheet Id': response.get('spreadsheetId'), 'spreadsheet title': response.get('properties').get('title'), } markdown = tableToMarkdown('Successfully created a spreadsheet', human_readable, headers=['spreadsheet Id', 'spreadsheet title']) results = CommandResults(readable_output=markdown, outputs_prefix='GoogleSheets.Spreadsheet', outputs_key_field='spreadsheetId', outputs=response) return results
def batch_update(sheets_service: Resource, body: Dict, spreadsheet_id: str): """ Peforms a Google Sheet batch update to apply one or more updates to a spreadsheet, like adding data validation or creating a new sheet. """ return ( sheets_service.spreadsheets() .batchUpdate(spreadsheetId=spreadsheet_id, body=body) .execute() )
def value_batch_clear(sheets_service: Resource, body: Dict, spreadsheet_id: str): """ Peforms a Google Sheet values batch clear to clear values in one or more ranges of a spreadsheet. """ return ( sheets_service.spreadsheets() .values() .batchClear(spreadsheetId=spreadsheet_id, body=body) .execute() )
def mark_row_as_published(google_spreadsheet_service: Resource, spreadsheet_id: str, row_range: str): sheet = google_spreadsheet_service.spreadsheets() result = (sheet.values().update( spreadsheetId=spreadsheet_id, range=row_range, valueInputOption='RAW', body={ 'majorDimension': 'COLUMNS', 'values': [['да']] }, ).execute())
def update_values( spreadsheet_id: ID, *, range_: Range, grid: Grid, raw: bool = False, sheets_service: discovery.Resource = None, ) -> None: """ Update range with given values. """ if sheets_service is None: sheets_service = sheets_api.build_service() input_mode = "RAW" if raw else "USER_ENTERED" body = {"values": grid} sheets_service.spreadsheets().values().update( spreadsheetId=spreadsheet_id, range=range_, valueInputOption=input_mode, body=body, ).execute()
def get_values(spreadsheet_id: ID, *, range_: Range, sheets_service: discovery.Resource = None) -> Grid: """ Query spreadsheet from provided range and return its values. """ if sheets_service is None: sheets_service = sheets_api.build_service() result = (sheets_service.spreadsheets().values().get( spreadsheetId=spreadsheet_id, range=range_).execute()) grid: Grid = result.get("values", [[]]) return grid
def update_spreadsheet(service: Resource, args: dict) -> CommandResults: ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: CommandResults Action: updates a spreadsheet by a user costume update request ''' spreadsheet_id = args.get('spreadsheet_id') request_to_update = safe_load_json(args.get('requests')) response = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=request_to_update).execute() readable_comment = 'updated the spreadsheet' return prepare_result(response, args, readable_comment)
def get_sheets_for_spreadsheet( service: Resource, spreadsheet_id: str ) -> List[SheetInfo]: """Returns the titles of the sheets in a provided Google Sheet.""" spreadsheet_info: Dict = service.spreadsheets().get( spreadsheetId=spreadsheet_id, fields="sheets.properties(sheetId,title)" ).execute() return [ { "title": sheet["properties"]["title"], "sheetId": sheet["properties"]["sheetId"], } for sheet in spreadsheet_info["sheets"] ]
def parse_spreadsheet(google_spreadsheet_service: Resource, spreadsheet_id: str): sheet = google_spreadsheet_service.spreadsheets() social_platform_headers_range = 'A2:C2' data_headers_range = 'D1:H1' data_range = 'A3:H14' first_data_row = 3 last_data_row = 14 headers_social = (sheet.values().get( spreadsheetId=spreadsheet_id, range=social_platform_headers_range).execute()) headers_data = (sheet.values().get(spreadsheetId=spreadsheet_id, range=data_headers_range).execute()) headers = [*headers_social['values'][0], *headers_data['values'][0]] data = (sheet.values().get(spreadsheetId=spreadsheet_id, range=data_range, valueRenderOption='FORMULA').execute()) df = pd.DataFrame(columns=headers, data=data['values']) df = df.rename( columns={ 'ВКонтакте': 'vk', 'Телеграм': 'telegram', 'Фейсбук': 'facebook', 'День публикации': 'publish_day', 'Время публикации': 'publish_time', 'Статья': 'article_id', 'Картинки': 'image_id', 'Опубликовано?': 'is_published', }) df['vk'] = df['vk'].apply(extract_boolean_value) df['telegram'] = df['telegram'].apply(extract_boolean_value) df['facebook'] = df['facebook'].apply(extract_boolean_value) df['publish_day'] = df['publish_day'].apply(lambda x: days_of_week[x]) df['publish_time'] = df['publish_time'] df['article_id'] = df['article_id'].apply( extract_google_drive_id_from_text) df['image_id'] = df['image_id'].apply(extract_google_drive_id_from_text) df['is_published'] = df['is_published'].apply(extract_boolean_value) df['target_column'] = [ f'H{index}' for index in range(first_data_row, last_data_row + 1) ] return df
def add_sheets( sheets_service: Resource, sheet_titles: SheetTitles, spreadsheet_id: str ): """Adds sheets for the provided titles to a Google Sheet.""" if not sheet_titles: return requests: List[Dict] = [] for sheet_title in sheet_titles: requests.append({"addSheet": {"properties": {"title": sheet_title}}}) body = {"requests": requests} return ( sheets_service.spreadsheets() .batchUpdate(spreadsheetId=spreadsheet_id, body=body) .execute() )
def create_sheet(service: Resource, args: dict) -> CommandResults: ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: CommandResults Action: creates a new sheet in a spreadsheet ''' spreadsheet_id = args.get('spreadsheet_id') rgb_format = argToList(args.get('tab_color')) rgb_format = [1, 1, 1, 1] if not rgb_format else rgb_format request_to_update = { "requests": [{ "addSheet": { "properties": { "sheetId": args.get('sheet_id', None), "title": args.get('sheet_title', None), "index": args.get('sheet_index', None), "sheetType": args.get('sheet_type', "GRID"), "rightToLeft": argToBoolean(args.get('right_to_left', False)), "tabColor": { "red": rgb_format[0], "green": rgb_format[1], "blue": rgb_format[2], "alpha": rgb_format[3] }, "hidden": args.get('hidden', False), } } }], "includeSpreadsheetInResponse": args.get('echo_spreadsheet'), } request_to_update = remove_empty_elements(request_to_update) response = service.spreadsheets().batchUpdate( spreadsheetId=spreadsheet_id, body=request_to_update).execute() readable_comment = 'created a new sheet' results = prepare_result(response, args, readable_comment) return results
def clear_sheet(service: Resource, args: dict) -> CommandResults: # pragma: no cover ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults): Command result with only readable output Action: Clears a sheet from a spreadsheet ''' spreadsheet_id = args.get('spreadsheet_id') ranges = args.get('range') request = service.spreadsheets().values().clear(spreadsheetId=spreadsheet_id, range=ranges, body={}) request.execute() # we don't save the response because there is no need for output or HR results = CommandResults( readable_output='### Successfully cleared the sheet' ) return results
def data_paste_sheets(service: Resource, args: dict) -> CommandResults: # pragma: no cover ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults): Command result with only readable output Action: Inserts data into a spreadsheet sheet ''' spreadsheet_id = args.get('spreadsheet_id') request_to_update = { "requests": [{ "pasteData": { "coordinate": { "sheetId": args.get('sheet_id'), "rowIndex": args.get('row_index'), "columnIndex": args.get('column_index'), }, "data": args.get('data'), "type": f"PASTE_{args.get('paste_type')}", } }], "includeSpreadsheetInResponse": args.get('echo_spreadsheet', None), } request_to_update = remove_empty_elements(request_to_update) kind = args.get('data_kind') paste_data = request_to_update.get('requests')[0].get('pasteData') # adding a field to the paste data sub dict so that it will fit the api call needed if kind == 'delimiter': paste_data[kind] = ',' else: paste_data[kind] = "true" request_to_update = remove_empty_elements(request_to_update) response = service.spreadsheets().batchUpdate( spreadsheetId=spreadsheet_id, body=request_to_update).execute() readable_comment = 'pasted the data' results = prepare_result(response, args, readable_comment) return results
def write_to_spreadsheet(service: Resource, spreadsheet_id: str, spreadsheet_range: str, df: pd.DataFrame, **kwargs) -> dict: value_input_option = kwargs.get('value_input_option', 'USER_ENTERED') values = df.fillna('').values.tolist() if kwargs.get('header', True): values.insert(0, df.columns.values.tolist()) body = { 'valueInputOption': value_input_option, 'data': [ { 'range': spreadsheet_range, 'values': values, }, ], } return service.spreadsheets() \ .values() \ .batchUpdate(spreadsheetId=spreadsheet_id, body=body) \ .execute()
def find_replace_sheets(service: Resource, args: dict) -> CommandResults: # pragma: no cover ''' Args: service (discovery Resource): google-api discovery resource (google api client) args (dict): demisto.args() for the api call Returns: (CommandResults): Command result with only readable output Action: Finds a vaule in the spreadsheets sheet and replaces it. ''' spreadsheet_id = args.get('spreadsheet_id') request_to_update = { "requests": [{ "findReplace": { "find": args.get('find'), "replacement": args.get('replacement'), "sheetId": args.get('sheet_id'), "allSheets": args.get('all_sheets'), "matchCase": args.get('match_case'), "matchEntireCell": args.get('match_entire_cell'), "range": { "sheetId": args.get('range_sheet_id'), "startRowIndex": args.get('range_start_row_Index'), "endRowIndex": args.get('range_end_row_Index'), "startColumnIndex": args.get('range_start_column_Index'), "endColumnIndex": args.get('range_end_column_Index'), } } }], "includeSpreadsheetInResponse": args.get('echo_spreadsheet'), } request_to_update = remove_empty_elements(request_to_update) response = service.spreadsheets().batchUpdate( spreadsheetId=spreadsheet_id, body=request_to_update).execute() readable_comment = 'found and replaced' results = prepare_result(response, args, readable_comment) return results
def load_sheets_as_dataframes( service: Resource, spreadsheet_id: str, range: str = "!A1:B1000", has_header_row: bool = True, ) -> Dict[SheetTitle, pd.DataFrame]: """Returns the contents of a spreadsheet as Pandas dataframes: { sheet_title1: content_dataframe1, ... } """ sheet_titles: SheetTitles = get_sheet_titles_for_spreadsheet( service, spreadsheet_id ) ranges = [sheet_title + range for sheet_title in sheet_titles] results: Dict = service.spreadsheets().values().batchGet( spreadsheetId=spreadsheet_id, ranges=ranges, fields="valueRanges(range,values)" ).execute() dataframes: Dict[SheetTitle, pd.DataFrame] = {} for result in results["valueRanges"]: if "values" not in result: continue range: str = result["range"] # the sheet title is the part of the string that comes before the "!" sheet_title: SheetTitle = range.split(SHEET_RANGE_SPLIT_CHAR, 1)[0] sheet_df: pd.DataFrame = pd.DataFrame.from_records(result["values"]) if has_header_row: sheet_df.columns = sheet_df.iloc[0] sheet_df = sheet_df.reindex(sheet_df.index.drop(0)) dataframes[sheet_title] = sheet_df return dataframes
def __init__(self, service: Resource, max_retry: int=0, sleep: int=5): self._service = service.spreadsheets() setattr(self, MAX_RETRY_ATTRIBUTE, max_retry) setattr(self, SLEEP_ATTRIBUTE, sleep)
def build_vfr_cost_of_delay_sheet(auth: ComboAuth, spreadsheet_id: int, service: Resource): sheet_add = { 'requests': { 'addSheet': { 'properties': { 'title': 'Cost of Delay' } } } } add_response = service.spreadsheets().batchUpdate( spreadsheetId=spreadsheet_id, body=sheet_add).execute() cli_library.echo('added cost of delay sheet') cod_sheet_id = add_response['spreadsheetId'] cod_grid_id = add_response['replies'][0]['addSheet']['properties'][ 'sheetId'] cli_library.echo('retrieving stories for cost of delay') # retrieve stories ordered by cost of delay jsonr = tasks.search_for_stories_ord_cod(auth) cli_library.echo('cost of delay stories retrieved') # build the data for the cost of delay sheet data = [{ 'range': 'Cost of Delay!A1:D1', 'values': [['Cost of Delay', 'Issue key', 'Summary', 'Duration']] }] row_idx = 2 # create an array of requests that will hold "updateCells" requests # these are rows that will have the background color changed update_cells_requests = [] update_cells = {'requests': update_cells_requests} # create a var to hold the "previous" cod value prev_cod = 0 cli_library.echo('generating cell data for cost of delay sheet') for issue in jsonr['issues']: # if idx > 2, then check to see if current duration val is different from "previous" val # if so, then add an updateCells request and increment counter, then append row of data # by default we always append a row of data if row_idx == 2: prev_cod = issue['fields']['customfield_18401'] if row_idx > 2 and issue['fields']['customfield_18401'] != prev_cod: update_cells_requests.append( build_color_update_details(cod_grid_id, row_idx - 1, row_idx, 0, 5)) prev_cod = issue['fields']['customfield_18401'] row_idx += 1 data.append( build_vfr_details('Cost of Delay!', row_idx, row_idx, issue)) row_idx += 1 update_cells_requests.append(build_auto_resize_details(cod_grid_id, 0, 3)) response = service.spreadsheets().batchUpdate(spreadsheetId=cod_sheet_id, body=update_cells).execute() cli_library.echo('cost of delay cell formats updated') body = {'valueInputOption': 'USER_ENTERED', 'data': data} result = service.spreadsheets().values().batchUpdate( spreadsheetId=cod_sheet_id, body=body).execute() cli_library.echo('cost of delay cell data updated')