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