UrlFetchApp with Service Account credentials in Google script throws errors with post, put, patch-CodePudding

I have a private test spreadsheet that is shared to the Service Account email for a GAS project. Below is:

A function to "get" data from that spreadsheet using UrlFetchApp with Service Account oAuth2 credentials. (Fetch has Range value: "'sName'!A1:B3"; sheet has Row 1 column headings: A - "uId"; B - "title"). It works fine with the spreadsheet shared (with edit rights) to the Service Account email. It fails for lack of authorization without that share. So, the oAuth credentials seem to be correct, with Scope of spreadsheets. (I'm working with this code by Spencer Easton to generate the oAuth clientToken for the service account.)
A function for updating (post, put, patch) the same test spreadsheet. It fails with all three methods, but with different error messages as follows:

Put: "Invalid JSON payload received. Unknown name "title": Cannot bind query parameter. Field 'title' could not be found in request message." If contentType:'application/json' is included in options, the error changes to: "Invalid JSON payload received. Unexpected token.\nuId=id3&title=Update\n^" (fetch updateRange value: "'sName'!A3:B3" or "'sName'!A4:B4")

Post: "The requested URL /v4/spreadsheets/[actual ssId]/values/'sName'!A4:B4 was not found on this server. That’s all we know."

Patch: same as Post, with updateRange= "'sName'!A3:B3"

I've gone through SO Q&A postings related to these errors. Some say the problem is including contentType in the options (or Content-Type: in the headers). That didn't fix things, but does change the error message with "put" method, as stated above. Google UrlFetchApp docs say it is not needed for updates (so below it is shown commented out).

I hope I'm just overlooking something basic in properly configuring the range or the options for updates using UrlFetchApp.

Thank you for any guidance in resolving this problem with updating.

GET DATA -- Correctly retrieves data

function getData(ssId,range) {
   if (!clientToken) { return "Authorization Failed";}
      var url= `https://sheets.googleapis.com/v4/spreadsheets/${ssId}/values/${range}`
      var options = {
             method:  'get',    
             contentType: 'application/json',
         headers: { Authorization: 'Bearer '   clientToken }
          };
      response = UrlFetchApp.fetch(url,options);
      return response;
    
 } //end getData function
 var fetchData = getData(ssId,range);

UPDATE DATA -- errors for post, put and patch

The update data object: var updateData = {'uId':'id3', 'title':'Update Title'}

function postData(ssId,updateRange,updateData) {
    if (!clientToken) {return "Authorization Failed";}
    var url= `https://sheets.googleapis.com/v4/spreadsheets/${ssId}/values/${updateRange}`
        var options = {
    //           contentType: 'application/json',  
             valueInputOption: 'RAW',
             method:   'put',     //patch, post, or put 
             payload: updateData,     
             headers: { Authorization: 'Bearer '   clientToken }
             };
     var response= UrlFetchApp.fetch(url,options);
     var responseCode = response.getResponseCode();
     var responseBody = response.getContentText();
    if (responseCode === 200) {
                var responseJson = JSON.parse(responseBody);
                return responseJson; 
      } else {
             Logger.log(Utilities.formatString("Request failed. Expected 200, got %d: %s", responseCode, responseBody))
                 return responseCode;
                 }      
   } //end postData function
 var dataUpdate = postData(ssId,updateRange,updateData);

CodePudding user response：

Unfortunately, I cannot confirm the method you want to use in your 2nd script. But, from your endpoint, I guessed that you might have wanted to "Method: spreadsheets.values.update". Ref

If my understanding is correct,

var updateData = {'uId':'id3', 'title':'Update Title'} cannot be directly used in this method.
There is not property of valueInputOption in UrlFetchApp.fetch().

If the header row has uId and title in the columns "A" and "B" and you want to put the values of id3 and Update Title to updateRange, how about the following modification?

From:

var url= `https://sheets.googleapis.com/v4/spreadsheets/${ssId}/values/${updateRange}`
    var options = {
//           contentType: 'application/json',  
         valueInputOption: 'RAW',
         method:   'put',     //patch, post, or put 
         payload: updateData,     
         headers: { Authorization: 'Bearer '   clientToken }
         };

To:

// var updateData = [['uId', 'title'], ['id3', 'Update Title']]; // If you want to include the header row, please use this.
var updateData = {values: [['id3', 'Update Title']]};

var url = `https://sheets.googleapis.com/v4/spreadsheets/${ssId}/values/${updateRange}?valueInputOption=RAW`;
var options = {
  contentType: 'application/json',
  method: 'put',
  payload: JSON.stringify(updateData),
  headers: { Authorization: 'Bearer '   clientToken }
};

Note:

I think that if your access token can be used for putting values to Google Spreadsheet, this modified script works.

From:

To:

Note:

References: