Using Google Sheet REST Api

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);

[Harshit Saini]

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]

Indeed. All things I have confirmed already by being able to READ from the file. Writing, however, not so much.

Answer 1

[Tahsin Hassan]

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.

https://developers.google.com/identity/protocols/oauth2

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.

https://www.mathworks.com/support/contact_us.html

Thanks