CODEWITHSUNDEEP
google-apps-scriptoauth-2.0google-sheets-api
Damian Drewulski
Damian Drewulski

Reputation: 124

OAuth 2.0 from Google Apps Script (GAS) to Sheets API

Based on the Google's documentation, I've implemented the following code to append a row to the Google Sheet (and I'm owner of this sheet).

function addSalaryLog(updateArray) {
  const resource = {
    "majorDimension": "ROWS",
    "values": [updateArray]
  };
  const spreadsheetId = 'here_goes_the_spreadsheet_id';
  const range = 'A:A';
  const optionalArgs = {valueInputOption: "USER_ENTERED"};
  Sheets.Spreadsheets.Values.append(resource, spreadsheetId, range, optionalArgs);
}

When I call it, in return I get the following error:

GoogleJsonResponseException: API call to sheets.spreadsheets.values.append failed with error: Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.

No need to ask, I clicked the link :-) It explains how to create an OAuth 2.0 in the developer console. Created ✅. But later it does not explain how to actually authorize the script. I expect that before calling the Sheets API, I should be able to trigger launching the permission dialog.

I see the following, possible options:

  1. Instead of Sheets.... use URLFetch.
  2. Trigger the authentication (preferred). Any tips here?

UPDATE I've implemented testAddSalaryLog() which is exactly the same as the original function but has hardcoded values. And it works! It makes me think that the solution is to force the permission screen to trigger.

Upvotes: 1

Views: 2169

Answers (1)

Giselle Valladares
Giselle Valladares

Reputation: 2261

I have 3 questions/suggestions:

Question 1.

Since you are using the Sheets API. Have you enabled the Google Sheets API advanced service in your script? From Editor > Service > Google Sheet API?

enter image description here

enter image description here

Question 2.

Have you reviewed if the "Sheets API scope" were added to the script by reviewing the project Overview? If they were not added automatically, you need to add them manually by following these steps:

  1. Access the 'Project Settings' to enable the manifest file in the editor. enter image description here

  2. Return to the editor and select 'appsscript.json' enter image description here

  3. Add the scopes:

    "https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/spreadsheets" enter image description here

Question 3.

Have you authorized the code already?

In the Apps Script editor, click Run. The first time you run the sample, it will prompt you to authorize access:

Select Review permissions > Allow.

enter image description here

Reference:

Upvotes: 2

Related Questions