File Exchange

image thumbnail

Matlab to Google Sheets (matlab2sheets)

version 1.0.0.0 (493 KB) by Andrew Bogaard
This function (and run-once helper function) exports data to a Google spreadsheet

10 Downloads

Updated 21 Sep 2017

GitHub view license on GitHub

Use this together with GetGoogleSpreadsheet from the File Exchange and you can read/write to Google Sheets from MATLAB. Inspired by an older set of functions (Matlab to Google Spreadsheets) that no longer works with Google's new v4 API.
--
status = mat2sheets(spreadsheetID, sheetID, sheetpos, d)
% This function takes values from an array or cell array and places them in
% a Google spreadsheet. It requires the one-time use of RunOnce (see below)
% SYNTAX: status = mat2sheets(spreadsheetID, sheetID, pos, d)
%
% ARGUMENTS:
% spreadsheetID: (string), identifier from URL of your Google Sheet
% sheetID: (string), another identier from URL
% pos: 1x2 array with indices for [sheetrow, sheetcolumn]
% to start pasting data
% d: array or cell array of data to paste into Sheet. If
% [], pos indicates row or range of rows [start stop]
% to delete
%
% RETURNS: status (0=failed, 1=success)
%
% EXAMPLES:
% For sheet with the following URL:
% https://docs.google.com/spreadsheets/d/1GPd-vBsX5VUejz5hrxE/edit#gid=552
%
% A call may look like:
% mat2sheets('1GPd-vBsX5VUejz5hrxE', '552', [2 3], [1 2 3 4 5])
%
% Would put values 1,2,3,4,5 into cells C2,D2,E2,F2,G2, respectively
%
% To delete row(s)
% mat2sheets('1GPd-vBsX5VUejz5hrxE', '552', 2, []) % delete row 2
% mat2sheets('1GPd-vBsX5VUejz5hrxE', '552', [2 10], []) % delete rows 2-10 inclusive
%
% USING RunOnce().
% Before using this code, you've got to enable the Drive/Sheets APIs via:
% https://console.developers.google.com/
% Here, you will "create credentials" via an OAuth 2.0 client ID that comes
% with a Client ID and Client Secret. These codes are the two arguments of
% RunOnce(client_id, client_secret). Run RunOnce with both of these
% codes passed as strings, and follow the instructions.
%
% The following code is inspired by, and makes use of, code
% originally published in the file exchange by Claudiu Giurumescu.
% (https://www.mathworks.com/matlabcentral/fileexchange/31221-matlab-to-google-spreadsheets)
%
% I wrote this to accomodate for latest changes in Google API, added some
% comments, and simplified it all so that it can be implemented by the
% average user (hopefully!)
%
% Additionally, I use loadjson by Qianqian Fang to read the input streams
% from Google for learning meta data about the sheets
% (https://www.mathworks.com/matlabcentral/fileexchange/33381-jsonlab--a-toolbox-to-encode-decode-json-files)
%
% andrew robert bogaard 26 sept 2016
% updated 25 july 2017 (delete rows)
% abogaard@uw.edu

Comments and Ratings (34)

Eventually I obtained the client secret, but when I try to RunOnce, I receive the following error
error: urlread: METHOD must be "get" or "post"
error: called from
RunOnce>getAccessToken at line 26 column 17
RunOnce at line 18 column 26

Perhaps the Console.Developers.Google.com has changed a little bit, and I'm not able to follow the guidelines of the ReadMe.PDF to set the Drive/Sheets APIs. After I access the API Library I click on Google Sheets and it shows that it is enabled. Then I click on credentials, I select create credentials, and select OAuth Client ID. In the next screen there is a warning that "To create an OAuth client ID, you must first set a product name on the consent screen". Clicking on the warning I the set a scope to edit google sheets, and it replied that it needs to request an authorization or something to that extent. Am I doing something wrong? Obviously I'm missing something.

marcio lima

to fix Pierre's issue do this:
in line 350 (under the pasteSheetData function), change:
'''valueInputOption'': ''RAW'',',...
to
'''valueInputOption'': ''USER_ENTERED'',',...

the apostrophe is a function of google sheets for formatting text as text rather than as numbers. For more info, look at this:
https://developers.google.com/sheets/api/guides/values
under the Writing section.

marcio lima

To fix issues regarding very large sheets make the following change:
under the "getSheetMetaData" function change the url to:
url = ['https://sheets.googleapis.com/v4/spreadsheets/' spreadsheetID '?&fields=sheets.properties'];

JFLabrie

Works for me so far. Thanks Andrew! Some notes that could be useful for others: I was not familiar at all with the "google credential" thing. I went to "https://console.developers.google.com/ ", had to create a project and name it. Then I eventually got to a page where I could create a credential then I obtained a client id and a client_secret. I executed RunOnce(client_id, client_secret) and followed the instruction displayed in Matlab. All went fine. Then I executed "status = mat2sheets(spreadsheetID, sheetID, [3 2], d)" and got a 403 error. Following comments from Chris, I went back to "https://console.developers.google.com/ " searched for "sheet" API and activated the Google Sheets API. Then I executed again "status = mat2sheets(spreadsheetID, sheetID, [3 2], d)" and got nothing back in the Matlab prompt. Matlab was in a loop. Running the code in debug mode, I ended up noticing that the following loop was going on for what seemed forever, while the variable json_return was growing bigger and bigger:
while ~isempty(l)
json_return = [json_return, l];
l = br.readLine();
end
I suspected my Google Sheet was too large (had already loads of data init) so I tested with a brand new blank Google Spread Sheet and it worked immediately.
Hopefully this can help someone somewhere.
Cheers.

Hi,

I dont really understand the use of the RunOnce function. Where and how we have to run this function ?

Thanks.

Thank you all for your input and feedback, I am very glad to see that the code is mostly working and that it is helpful. I've been busy with an international move this summer, and haven't had time to keep up with requests. I will try to be more responsive now that I am settled.

Regarding the slow upload time for large datasets, I built in a delay between requests because I found that Google would bounce back requests if they came too fast/numerous. This may be optimized by changing how the data is uploaded (instead of iterating through a with multiple requests, perhaps we can try to upload more data per request.

Generally speaking, I didn't have large datasets in mind when I wrote this code. I briefly considered storing data in sheets, but it was just too clunky and limited, so I decided that it was better suited for metadata (which is how I use it).

Pierre, I will look into your apostrophe issue in the coming weeks.

This is especially true if there is a large amount of conditional formatting throughout the sheet.

Jan Siroky

Thanks for this tool and clear instructions.

Thanks a lot for this powerful and well structured code!
Is there also a way to READ data, like just by skipping the Input "d"? It seems that the is no spreadsheets.values.get request defined.

Hello,

First of all thank you a lot for this powerful code.

It works well for me, excepted one thing that I didn't figure out for the moment. I have a cell array containing numbers, strings, dates and hyperlinks. When I run the code to copy this whole array to a Google Sheet:
* for numbers and strings, it works fine.
* for dates (like {'2018/01/01'}) and for hyperlinks (like {'=HYPERLINK('something.com', 'linkName')'}), the cell is copied to the Google Sheet but there is always an 'apostrophe' just before. For instance: for the cell {'2018/01/01'} I will have '2018/01/01 in the Sheet (see the ' symbol) instead of 2018/01/01. This is a problem especially because the hyperlink will not be displayed correctly ('=HYPERLINK()).

Thank you in advance for your help.

hxen

Hello all. This pertains to John Wang's issue (below) that I too encountered. Most likely, the Google Sheets API was not enabled for the project. Both Google Drive API and Google Sheets API must be enabled for mat2sheets to work. Also, be sure to run RunOnce and in NOT "hit any key" until AFTER you have allowed access to the project to your drive and sheets via the Google/com/device link accessed through the user code supplied to you by RunOnce. After you click "allow" on Google.com/device, then return back to your Matlab command screen and hit any key. You should be all good to then run mat2sheets after that. Failure to execute RunOnce in this sequence or enable third-party OAuth access to Sheets will result in the error John Wang got. Hope this helps anyone who might run into this (or hopefully avoid it altogether who are new to APIs). And thanks to Andrew for writing this useful code.

An anonymous messenger (thanks!) had a similar issue to Quang Ha Vo below, which, as far as I can tell, has to do with how java and matlab build an object at that step. I have added a check for this strange behavior, please let me know if it fixes the following error:

Error in mat2sheets>getSheetMetaData (line 369)
sIds(i) = jdat.sheets(i).properties.sheetId;
Error in mat2sheets (line 73)
[nR, nC, sheetName] = getSheetMetaData(spreadsheetID, sheetID, aSheets); % to ensure we have enough cells"

Quang Ha Vo

Hello Andrew, many thanks for your code, but I got the following errors:
Improper index matrix reference.

Error in mat2sheets>getSheetMetaData (line 369)
sIds(i) = jdat.sheets(i).properties.sheetId;

Error in mat2sheets (line 73)
[nR, nC, sheetName] =
getSheetMetaData(spreadsheetID, sheetID, aSheets); %
to ensure we have enough cells
could u please give me some suggestions?

If looking for help, please write me directly using the email address provided in the code header (also above). If your problem might affect other users, I will add it here myself.

John Wang

Hi Andrew, I got some problems when I executed.
Would you give me a help?Thanks.
Error using urlreadwrite (line 56)
Could not POST to URL.

Error in urlread (line 36)
[s,status] = urlreadwrite(mfilename,catchErrors,url,varargin{:});

Error in mat2sheets>refreshAccessToken (line 400)
newAccessTokenString=urlread('https://accounts.google.com/o/oauth2/token','POST', ...

Error in mat2sheets (line 55)
aSheets = refreshAccessToken; % refresh and retrieve access token

David Cagle

Thanks for the comments on how to append data to an existing spreadsheet Via "getGoogleSPreadsheet"...

Has anyone had experience successfully doing this from a Corporate Gmail account. I can get the functions working from a personal gmail but have not been able to figure out what to change to make it work for a corporate account.

"s = GetGoogleSpreadsheet(spreadsheetid); % gets spreadsheet
mat2sheets(spreadsheetid, sheetid, [size(s,1)+1,1], [1,2,3]); % example 1
mat2sheets(spreadsheetid, sheetid, [size(s,1)+1,1], {'one', 'two', 'three'}); % example 2"

To give everyone an update: My problem was I did not have the Google Sheets API enabled, only the Google Drive API. I thought they were one in the same. Although I ultimately figured this out myself, Andrew was incredibly prompt in his response and eager to assist.

Let me know if I can send you a donation to get this sorted out. walkinglime@gmail.com

I'm getting this error, but I've set up all my credentials and everything

Failed gathering metadata. Last response was: 403/Forbidden
Failed pasting data. Last response was: 403/Forbidden

Hi Andrew, internet is working. I do need to go through a proxy, I believe this could generate the problem?

HI Giovanni - apologies if this sounds like dumb advice, but is your internet working properly? Did you follow the documentation? I just tested and all is working from here.

If these don't work, it may be something about your system and would require more information.

Andrew

Hi, I encounter this problem when executing: Any hints? thanks

Error using mat2sheets>getSheetMetaData (line 267)
Java exception occurred:
java.net.UnknownHostException: sheets.googleapis.com

at java.net.AbstractPlainSocketImpl.connect(Unknown Source)

at java.net.PlainSocketImpl.connect(Unknown Source)

at java.net.SocksSocketImpl.connect(Unknown Source)

at java.net.Socket.connect(Unknown Source)

at sun.security.ssl.SSLSocketImpl.connect(Unknown Source)

at sun.security.ssl.BaseSSLSocketImpl.connect(Unknown Source)

at sun.net.NetworkClient.doConnect(Unknown Source)

at sun.net.www.http.HttpClient.openServer(Unknown Source)

at sun.net.www.http.HttpClient.openServer(Unknown Source)

at sun.net.www.protocol.https.HttpsClient.<init>(Unknown Source)

at sun.net.www.protocol.https.HttpsClient.New(Unknown Source)

at
sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.getNewHttpClient(Unknown
Source)

at sun.net.www.protocol.http.HttpURLConnection.plainConnect(Unknown Source)

at sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.connect(Unknown Source)

at sun.net.www.protocol.http.HttpURLConnection.getInputStream(Unknown Source)

at java.net.HttpURLConnection.getResponseCode(Unknown Source)

at sun.net.www.protocol.https.HttpsURLConnectionImpl.getResponseCode(Unknown Source)

Error in mat2sheets (line 53)
[nR, nC, sheetName] = getSheetMetaData(spreadsheetID, sheetID, aSheets); % to ensure we have
enough cells

Hi Cedric, things are a bit busy right now but I will put this on the to-do list. I'm glad the code is working for you.
Andrew

Hi Henry, thanks for your feedback! Here is how I would implement what you want to do (requires GetGoogleSpreadsheet https://www.mathworks.com/matlabcentral/fileexchange/39915-getgooglespreadsheet):
spreadsheetid = 'XXXX';
spreadsheetid = 'XXXX';
sheetid = '0';
s = GetGoogleSpreadsheet(spreadsheetid); % gets spreadsheet
mat2sheets(spreadsheetid, sheetid, [size(s,1)+1,1], [1,2,3]); % example 1
mat2sheets(spreadsheetid, sheetid, [size(s,1)+1,1], {'one', 'two', 'three'}); % example 2

Cedric Tsui

Andrew, would you be willing to write a utility that pulls the values from a particular cell?
getgooglespreadsheet does not parse the sheet correctly if you use newline characters within a cell.

Cedric Tsui

Incredibly useful!

I've run the RunOnce file, but how do I get the url for the spreadsheet?

Henry Hsiao

Thanks :]

Henry Hsiao

Henry Hsiao

Many thanks! it works! 2 additional questions hope you can help:
1. how to append row data to the last row
2. how to read from sheet ?
thank you

Updates

1.0.0.0

updated documentation

1.0.0.0

Added functionality to delete rows

% To delete row(s)
% mat2sheets('1GPd-vBsX5VUejz5hrxE', '552', 2, []) % delete row 2
% mat2sheets('1GPd-vBsX5VUejz5hrxE', '552', [2 10], []) % delete rows 2-10 inclusive

1.0.0.0

-

1.0.0.0

removed beta release

1.0.0.0

added urlreadwrite to mat2sheets

1.0.0.0

--

1.0.0.0

trivial metadata updates

1.0.0.0

This is a beta version, not extensively tested. Please let me know what needs fixing! :)

MATLAB Release Compatibility
Created with R2014a
Compatible with any release
Platform Compatibility
Windows macOS Linux