At work, we have nothing in place to supply ad hoc reports, I'm therefore frequently (but not often enough to raise the priority of enabling ad hoc reports) asked to generate a report manually. The result is nearly always a 2d array of data, and my company already uses Google Suites. Additionally, my primary language at work happens to be Python, a known first class citizen in the world of Google. Put that all together, and it seemed obvious that it would be simple to create a script that would fetch and format the data, then push it into a new spreadsheet.

As it turns out, this wasn't nearly as easy as I thought it would be, or should be. First, the current quick start guide uses a deprecated auth library. Also, because the new library is more in touch with how Google wants developers to use APIs, it actually takes far less code that the quick start guide suggests, but additional setup steps.

First, I needed to generate credentials. I feel like this has changed a lot since I last attempted to use a Google API (that was probably at least 5 years ago though...), and I'm not sure there aren't alternatives, but what worked for me was the following:

1. - Go to the Google Cloud console (No, I didn't need to setup an actual Google Cloud account...)
2. - Go to 'IAM & Admin' > 'Service accounts'
3. - Click on 'Create Service Account'
4. - Pick a name
5. - For Role, select 'Project' > 'Editor'
6. - Click 'Furnish a new private key', leave JSON selected
7. - Click 'Enable G Suite Domain-wide Delegation'
8. - Click 'Create'

  
1  export GOOGLE_APPLICATION_CREDENTIALS=just_downloaded_credentials.json

Now that we have credentials in place, lets install some libraries. I'm using Python 3, though the docs suggest some if not all Google libraries still support Python 2.

  
1    python3 -m venv ~/.venv/googleapi
2    pip install google-api-python-client
3    pip install google-auth
4    pip install google-auth-httplib2

Setup is done, lets write some code!

  
1      data = []
2      for i in range(4):
3        data.append([4, 3, 2])
4 
5      create("Test Doc", data, owner="ckhrysze@gmail.com")

This is all I really want to get working. The next step will create a new spreadsheet, figure out the area to update based on the size of the data, then update the sheet with the given data.

  
 1  def create(title='NoTitle', data=[], owner=None):
 2    '''
 3    Use the sheets API to create a sheet
 4    '''
 5    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?version=v4')
 6    service = discovery.build('sheets', 'v4', discoveryServiceUrl=discoveryUrl)
 7 
 8    body = dict(properties=dict(title=title))
 9    spreadsheet = service.spreadsheets().create(body=body).execute()
10    spreadsheet_id = spreadsheet['spreadsheetId']
11 
12    # 96 is the ascii value of the character before 'a'
13    last_column = 96 + len(data[0])
14    col = str(chr(last_column))
15    rows = len(data)
16    update_body = dict(values=data)
17    sheet_range = 'a1:{}{}'.format(col, rows)
18 
19    service.spreadsheets().values() \
20                          .update(spreadsheetId=spreadsheet_id,
21                                  valueInputOption='RAW',
22                                  range=sheet_range,
23                                  body=update_body) \
24                          .execute()

Finally, use the Google Drive API to change the owner.

  
 1  def change_owner(spreadsheet_id, owner):
 2    '''
 3    Use the drive api to change the owner
 4    '''
 5    drive_service = discovery.build('drive', 'v3')
 6 
 7    permission = drive_service.permissions().create(
 8      fileId=spreadsheet_id,
 9      transferOwnership=True,
10      body={
11        'type': 'user',
12        'role': 'owner',
13        'emailAddress': owner,
14      }
15    ).execute()
16 
17    drive_service.files().update(
18      fileId=spreadsheet_id,
19      body={'permissionIds': [permission['id']]}
20    ).execute()

Putting it all together, we get this: