using an http request to update google spreadsheet

Question

Trying to set up a spreadsheet to take in data via an http post. To test it, I set up a simple python script to just send an http request. I dont want to use a specific google api on python or somewhere else, because I want some other people to be able to simply send a request how they would like. So, In my google script I just have:

function doPost(e){
   sheet = SpreadsheetApp.getActiveSheet();
   range = sheet.getRange(1, 1)
   ange.setValue(e.text)
}

In python I simply have:

import requests

if __name__== "__main__":
    params = {'Authorization': "Bearer [token]",
              'contentType': 'application/json',
              'text': "is working?"}
    r = requests.post("https://script.google.com/macros/s/[uniquekey]/exec", params)
    print(r.status_code, r.reason)

All I get is

"401 Unauthorized"

I've also tried sending this over python as well as running JS in a webserver and through chrome (which i guessed raised security issues). everything gave me the same response. I'm sure I'm just not authorizing properly, but I havent been able to find the correct way to do it. thanks in advance. I'm sure theres some other issues too, but I cant seem to even get access.

Answer 1

How about this modification?

Modified script:

In this modified script supposes as follows.

• Web Apps is deployed as
  • Execute the app as is Me
  • Who has access to the app is Only myself or Anyone
• Your access token can be used for accessing to your Web Apps. If your access token cannot be used for it, please try to set the Web Apps as follows. - Execute the app as is Me - Who has access to the app is Anyone, even anonymous - In this setting, you can access to Web Apps without the access token.

Python:

import requests

if __name__== "__main__":
    params = {
        'text': "is working?",
    }
    headers = {
        'Authorization': "Bearer [token]",
    }
    r = requests.post("https://script.google.com/macros/s/[uniquekey]/exec", data=params, headers=headers)
    print(r.status_code, r.reason)

Note:

• Please put the access token to the header.

Google Apps Script:

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

function doPost(e) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange(1, 1);
  range.setValue(e.parameter.text);
  return ContentService.createTextOutput("Done.");
}

Note:

• By returning ContentService.createTextOutput(), Web Apps returns the status code of 200.
• You can retrieve the value of 'text': "is working?" as e.parameter.text.
• When you use SpreadsheetApp.getActiveSheet(), the value of e.parameter.text is put to the 1st page of Spreadsheet.

References:

• Web Apps
• ContentService

In my environment, I could confirm that this modification worked. But if in your environment, this modified scripts didn't work, I'm sorry.