Clear Filters
Clear Filters

Using Google Sheet REST Api

31 views (last 30 days)
Dennis Premoli
Dennis Premoli on 12 Feb 2023
Answered: Tahsin Hassan on 23 Feb 2023
HI everyone,
I've been trying to use the REST Api for Google Sheets to append a row of data to a a specific sheet in a GSheets file.
I have a valid API key and Sheet ID (which I tested by retrieving the data from the sheet successfully) but when I then try to use the API to write something, I get a 401 authorisation error or a 404.
An example of the table to append to can be found below:
The code is the following (API Key and SheetID ahve been replaced for obivous reasons).
% Define API Key and Sheet ID
apiKey = 'API_KEY';
sheetID = 'SheetID';
sheetName = 'SheetName'; % Name of the sheet you want to append data to
% Define the new data to be appended as a row
newData = {'Test1','Test2','Test3','Test4','Test5','Test6','Test7','Test8','Test9'};
% Define the request URL
requestUrl = ['https://sheets.googleapis.com/v4/spreadsheets/' sheetID '/values/' sheetName '!A1:append?valueInputOption=USER_ENTERED&key=' apiKey];
% Define the request body as a JSON structure
requestBody = struct('values',{newData});
% Send the POST request to the Google Sheets API using webread
options = weboptions('MediaType','application/json');
response = webwrite(requestUrl, requestBody, options);
The error I receeived back is the following:
Error using matlab.internal.webservices.HTTPConnector/copyContentToByteArray
The server returned the status 401 with message "" in response to the request to URL
https://sheets.googleapis.com/v4/spreadsheets/{SheetID}/values/{Sheetname}A1:append?valueInputOption=USER_ENTERED&key={APIKey}.
Error in readContentFromWebService (line 46)
byteArray = copyContentToByteArray(connection);
Error in webwrite (line 139)
[varargout{1:nargout}] = readContentFromWebService(connection, options);
Error in testGoogleAPI (line 46)
response = webwrite(requestUrl, requestBody, options);
  2 Comments
Harshit Saini
Harshit Saini on 15 Feb 2023
The 401 error you are receiving indicates that your API key is not authorized to access the requested resource. Make sure that you have enabled the Google Sheets API in your Google Cloud Console, and that you have created a project and associated it with the API key you are using.
In addition, you need to make sure that you have granted the necessary permissions to the Google Sheets file you are trying to access. You can do this by sharing the file with the email address associated with the API key you are using, or by granting access to the API key itself.
Dennis Premoli
Dennis Premoli on 15 Feb 2023
Indeed. All things I have confirmed already by being able to READ from the file. Writing, however, not so much.

Sign in to comment.

Answers (1)

Tahsin Hassan
Tahsin Hassan on 23 Feb 2023
Hi Dennis,
Google's api keys are usually designed to read from public resource.
So, for GoogleSheets this usually means, if you have a publicly shared googlehseet you can read from it, using an API key. This also means you can only use GET https methods , if using API keys.
However, if the sheet is
  • either private , and you want to read(invoke GET https methods)-write to it(invoke PUT or POST methods)
  • or public ( and you cannot write to it, to begin with)
you may not be able to use API keys. In those workflows, you need to obtain an OAuth Access Token for your private googlesheets and use that in your REST api calls.
Implementing the right OAuth protocol can become a bit tricky, depending on which kind of MATLAB you are running (MATLAB Desktop vs. MATLAB Online) and your organizational security requirements.
I would be interested to learn more about your exact requirements/ situations. Could you please connect with our tech support and ask them to connect you with the folks that own readtable / Data import export team.
Thanks

Products


Release

R2022a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!