How Can One Write To A Publicly Available Google Sheet (without Authorization) In Python?

March 31, 2023 Post a Comment

I have full edit access to a Google Sheet not owned by me. I want to be able to write to the spreadsheet using Python without Google API authorization. I checked several available

Solution 1:

I believe your goal as follows.

You want to put the values to the publicly shared Google Spreadsheet using python.
In this case, you want to access to the Spreadsheet without authorization.

For this, how about this answer?

Issue and workaround:

In order to put the values to the publicly shared Google Spreadsheet, the POST method is used. In this case, it is required to use the access token. On the other hand, in the case of the GET method, when the Sheets API is used, an API key can be used. And the endpoints like exportLinks, you can retrieve the values without the API key. These are the specification of Google side.

Under this condition, in order to achieve your goal, I would like to propose the following 2 patterns.

Pattern 1:

In this pattern, I would like to propose to access to the Spreadsheet using the access token retrieved from the service account. In this case, the script can be simpler.

Sample script:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

spreadsheetId = "###"  # Please set the Spreadsheet ID.

scope = ['https://www.googleapis.com/auth/spreadsheets']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
worksheet = spreadsheet.sheet1
worksheet.update_acell('A1', 'sample')

In this sample script, sample is put to the cell "A1" of the 1st tab in the publicly shared Spreadsheet using gspread.

Pattern 2:

In this pattern, I would like to propose to access to the Spreadsheet using the Web Apps created by Google Apps Script as the wrapper API. In this case, the python script is more simpler.

Usage:

Please do the following flow.

1. Create new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Prepare script.

Please copy and paste the following script (Google Apps Script) to the script editor. And please enable Google Sheets API at Advanced Google services. This script is for the Web Apps.

function doPost(e) {
  try {
    const spreadsheetId = e.parameter.spreadsheetId;
    const obj = JSON.parse(e.postData.contents);
    const resource = obj.body;
    const range = obj.arguments.range;
    const valueInputOption = obj.arguments.valueInputOption;
    Sheets.Spreadsheets.Values.update(resource, spreadsheetId, range, {valueInputOption: valueInputOption});
    return ContentService.createTextOutput("ok");
  } catch(e) {
    return ContentService.createTextOutput(JSON.stringify(e));
  }
}

In this case, the POST method is used.
In this sample script, as a test script, the values are put to the Spreadsheet using the method of spreadsheets.values.update in Sheets API.

3. Deploy Web Apps.

On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
Select "Me" for "Execute the app as:".
- By this, the script is run as the owner.
Select "Anyone, even anonymous" for "Who has access to the app:".
- In this case, no access token is required to be request. I think that I recommend this setting for your goal.
- Of course, you can also use the access token. At that time, please set this to "Anyone".
Click "Deploy" button as new "Project version".
Automatically open a dialog box of "Authorization required".
1. Click "Review Permissions".
2. Select own account.
3. Click "Advanced" at "This app isn't verified".
4. Click "Go to ### project name ###(unsafe)"
5. Click "Allow" button.
Click "OK".
Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
- When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

4. Run the function using Web Apps.

This is a sample python script for requesting Web Apps. Please set your Web Apps URL, Spreadsheet ID and range.

import json
import requests

spreadsheet_id = '###'  # Please set the Spreadsheet ID.
body = {
    "arguments": {"range": "Sheet1!A1", "valueInputOption": "USER_ENTERED"},
    "body": {"values": [["sample"]]}
}
url = 'https://script.google.com/macros/s/###/exec?spreadsheetId=' + spreadsheet_id
res = requests.post(url, json.dumps(body), headers={'Content-Type': 'application/json'})
print(res.text)

In this sample script, sample is put to the cell "A1" of the 1st tab in the publicly shared Spreadsheet.
In this case, no authorization is required in the python script, because it has already been done when Web Apps is deployed.

Note:

When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

Python Dictionary