CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-apiurlfetch
JSmith
JSmith

Reputation: 4808

Is this possible to use Sheets API post endpoint with UrlFetchApp?

I'm trying to append data to a spreadsheet.

I use UrlFetchApp to GET values but is it possible to POST values, for example, to append rows to a spreadsheet?

Here is my code so far

var token = ScriptApp.getOAuthToken();
var options = {
  "headers": {
       'Authorization': 'Bearer ' +  token
   },
  "method": "POST",
  "Content-type" : "application/json",
  "payload": JSON.stringify(values) //stringify a 2D array of data
}

var resp = UrlFetchApp.fetch("https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/RangeName:append?insertDataOption=INSERT_ROWS", options);

Upvotes: 1

Views: 615

Answers (1)

Tanaike
Tanaike

Reputation: 201428

It is possible to use Sheets API post URLs with UrlFetchApp. So how about this modification?

Modification points:

  • Please use valueInputOption to the query parameter.
    • In your endpoint, I think that an error of valueInputOption' is required but not specified occurs.
  • When you want to use Content-type, please add it to the headers.
    • If you want to use the content type outside of the headers, please use contentType: "application/json".

Modified script:

// sample values
var values = {"values":[["a1","b1","c1"],["a2","b2","c2"]]};
var spreadsheetId = "### spreadsheetId ###";
var RangeName = "### RangeName ###";

var token = ScriptApp.getOAuthToken();
var options = {
  "headers": {
       'Authorization': 'Bearer ' +  token,
       "Content-type": "application/json", // Modified
   },
  "method": "POST",
  "payload": JSON.stringify(values) //stringify a 2D array of data
}
var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "/values/" + RangeName + ":append?insertDataOption=INSERT_ROWS&valueInputOption=USER_ENTERED"; // Modified
var resp = UrlFetchApp.fetch(url, options);

Note:

  • Before you run this script, please confirm whether Sheets API is enabled at API console.

References:

If this was not what you want, please tell me. I would like to modify it.

Upvotes: 1

Related Questions