def _create_spreadsheet(sheets_client: discovery.Resource) -> str:
"""
:return: spreadsheetId
"""
request = {
"properties": {"title": "integration_test_spreadsheet"},
"sheets": [{"properties": {"title": "sheet1"}}, {"properties": {"title": "sheet2"}}],
}
spreadsheet = Spreadsheet.parse_obj(sheets_client.create(body=request).execute())
spreadsheet_id = spreadsheet.spreadsheetId
rows = [["header1", "irrelevant", "header3", "", "ignored"]]
rows.extend([f"a{i}", "dontmindme", i] for i in range(300))
rows.append(["lonely_left_value", "", ""])
rows.append(["", "", "lonelyrightvalue"])
rows.append(["", "", ""])
rows.append(["orphan1", "orphan2", "orphan3"])
sheets_client.values().batchUpdate(
spreadsheetId=spreadsheet_id,
body={"data": {"majorDimension": "ROWS", "values": rows, "range": "sheet1"}, "valueInputOption": "RAW"},
).execute()
sheets_client.values().batchUpdate(
spreadsheetId=spreadsheet_id,
body={"data": {"majorDimension": "ROWS", "values": rows, "range": "sheet2"}, "valueInputOption": "RAW"},
).execute()
return spreadsheet_id
def create_sheet(
self, student: Student,
spreadsheets_resource: discovery.Resource) -> GoogleSpreadsheet:
"""Add the raw data to the sheet."""
target_schools = TargetSchool.objects.filter(student=student)
target_schools = target_schools.select_related("school",
"school_application")
prefetch = Prefetch("milestones",
queryset=Milestone.objects.all().order_by("date"))
target_schools = target_schools.prefetch_related(prefetch)
target_schools = target_schools.order_by("school__name")
row_data = [self.build_header_row()]
row_data.extend(self.build_school_rows(target_schools))
data = {
"properties": {
"title": "{} - Application Status".format(student)
},
"sheets": [{
"properties": {
"gridProperties": {
"frozenRowCount": 1,
"frozenColumnCount": 1
}
},
"data": [{
"startRow": 0,
"startColumn": 0,
"rowData": row_data
}],
}],
}
response = spreadsheets_resource.create(body=data).execute()
return GoogleSpreadsheet(
response["spreadsheetId"],
response["sheets"][0]["properties"]["sheetId"],
target_schools.count(),
)