Google Sheet Update Api : Getting error [Request is missing required authentication credential]

0
votes

I am calling Google Sheet Update Request in Postman with following parameters:

Url:https://sheets.googleapis.com/v4/spreadsheets/SpreadSheetId/values/SheetName!A1:D5 Request Type : PUT
Headers : Authorization - MyAccessToken
Request Body :

enter image description here

Getting Response :
enter image description here

Note : For Updating into Spreadsheet, I follow these steps :

  1. Create an application on Google Api Console using this link and then move to Credentials section. Click Create Credentials, then OAuth Client ID, Create Application type as Web Application. For now leave the Restrictions Section. Then Create. Now Application is created.

  2. Add some project Name in the OAuth Consent Screen Tab

  3. Now, Authorize the API using this link. In the right corner, open the Settings Icon. Select Use your own OAuth Credentials as true. Add the Client Id and Client secret from the Project on Google Api Console. Select the Scope as https://www.googleapis.com/auth/spreadsheets under the Google Sheet Api v4 in the dropdown, then Authorize the API.

If Get redirect_mismatch_uri. then Copy the redirect URI till the oathPlayground or before the prompt parameter and paste it into the Authorized Redirect URI under the restrictions Section of project in Google Api Console. Then Access Token will be generated,

  1. Get Access Token. Copy this access token in the request which I defined earlier.

But Getting error. I am not able to figure out the problem. Thanks in Advance..

1
apioauthgoogle-sheetsgoogle-sheets-api
When I read your question, I worried about the situation of Sheet API v4. Can you confirm whether Sheet API v4 has been enabled at API console again? If Sheet API v4 is enabled, you can see it at the dashboard. I don't know know whether this will be a solution for you. I'm sorry. – Tanaike
Yes, sheet API 4 is enabled. Actually, I am able to read the data from spreadsheet using API key. But, not able to write the data into spreadsheet using OAuth token as OAuth token is mandatory for writing i think. – Juhi Matta
Thank you for confirming it. As an another confirmation, how about the information of access token? You can retrieve it using curl 'https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=### access token ###'. The response includes scopes and the expiration time. If the access token is no problem, it is necessary to think about the request method. – Tanaike
No, I am not able to retrieve this access token info. I am getting error ie 'either access_token, id_token, or token_handle required'. I think, there is a problem while generating the access token. Can you please help in that. – Juhi Matta
Thank you for reporting the result. In your case, your access token may be over the expiration time. So I have a next question. Do you have refresh token? – Tanaike

1 Answers

3
votes

Since you already have a refresh token, you can retrieve access token using refresh token. As a sample, it shows a method of how to retrieve access token using refresh token by curl. The access token retrieved by refresh token has the expiration time. Please confirm it.

You can see the detail infomation here. https://developers.google.com/identity/protocols/OAuth2WebServer

Curl command :

curl -L \
  --data "refresh_token=### refresh token ###" \
  --data "client_id=### client id ###" \
  --data "client_secret=### client secret ###" \
  --data "grant_type=refresh_token" \
  https://www.googleapis.com/oauth2/v4/token

Result :

{
 "access_token": "### access token ###",
 "token_type": "Bearer",
 "expires_in": 3600
}

Below curl command is for retrieving the information of access token. If the expiration time of access token had been over, the response is {"error_description": "Invalid Value"}. If it's not, you can see a following response.

Retrieve information of access token :

curl -L \
 --data "access_token=### access token ###" \
https://www.googleapis.com/oauth2/v2/tokeninfo

Result :

{
 "issued_to": "#####",
 "audience": "#####",
 "scope": "#####",
 "expires_in": 1234,
 "access_type": "offline"
}

EDIT1

This sample code is for updating the range of sheet1!A1:C2 to [["test1","test2","test3"],["test4","test5","test6"]].

Sample code :

curl -L -X PUT \
-H "Authorization: Bearer ### access token ###" \
-H "Content-Type: application/json" \
-d '{"values":[["test1","test2","test3"],["test4","test5","test6"]]}' \
"https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###/values/sheet1%21a1%3ac2?valueInputOption=USER_ENTERED"

Result :

{
  "spreadsheetId": "### spreadsheet ID ###",
  "updatedRange": "sheet1!A1:C2",
  "updatedRows": 2,
  "updatedColumns": 3,
  "updatedCells": 6
}