File Exchange

image thumbnail

Matlab to Google Spreadsheets

version 1.3.0.0 (22 KB) by Claudiu Giurumescu
Matlab functions to send data to Google Spreadsheets.

2 Downloads

Updated 23 Dec 2016

View License

WARNING: This code does not work anymore with the current Google Sheets API. Please use the Matlab2Sheets project below.
This set of Matlab functions will allow creating Google
spreadsheets, adding worksheets to them, modifying the
worksheets, and placing data in them.

Comments and Ratings (22)

These scripts worked great for a long time, but now I keep getting error "Last response was: 404/Not Found" even if I try just Matlab2GoogTest

I am getting "Last response (POST) was: 403/Forbidden", though I have entered my cliend id and secret, and also given the permissions for OAuth2. Are there other permissions Im missing?

I've been playing around with it for a while, and can't get passed "Error using urlreadwrite
Too many input arguments."
Can anyone point me in the right direction?

Matthias

Looks cool, but the Matlab2GoogTest.m script does not work for me. After fixing some errors, I got it to upload files to google, but the spread sheets always arrive empty except for the A1 cell, which contains a string like "[B@74727a7d" or similar. What could be the problem? Thanks!

Scott

Here is the code to update an entire row with one batch update:
function editWorksheetRow(spreadSheetKey,workSheetKey,row,columnNumbers,cellValues,aToken)

import java.io.*;
import java.net.*;
import java.lang.*;
com.mathworks.mlwidgets.html.HTMLPrefs.setProxySettings

MAXITER=10;
success=false;

safeguard=0;
nCols = 15;
while (~success && safeguard<MAXITER)
safeguard=safeguard+1;
nEntries = length(columnNumbers);

% Get edit details
urlCell=['https://spreadsheets.google.com/feeds/cells/' spreadSheetKey '/' workSheetKey '/private/full?return-empty=true&min-row=' num2str(row) '&max-row=' num2str(row) '&min-col=1&max-col=' num2str(nCols)];
con = urlreadwrite(mfilename,urlCell);
con.setInstanceFollowRedirects(false);
con.setRequestMethod( 'GET' );
con.setDoInput( true );
con.setRequestProperty('Content-Type','application/atom+xml;charset=UTF-8');
con.setRequestProperty('Authorization',['Bearer ' aToken]);
if (con.getResponseCode()~=200)
con.disconnect();
continue;
end

xmlData=xmlread(con.getInputStream());
con.disconnect(); clear con;

getURLStringList=strcat('https://spreadsheets.google.com/feeds/cells/',spreadSheetKey,'/',workSheetKey,'/private/full/batch');
con = urlreadwrite(mfilename,getURLStringList);
con.setInstanceFollowRedirects(false);
con.setRequestMethod('POST');
con.setDoOutput( true );
con.setRequestProperty('Content-Type','application/atom+xml;charset=UTF-8');
con.setRequestProperty('Authorization',['Bearer ' aToken]);

event = '';
event=['<feed xmlns=''http://www.w3.org/2005/Atom'' ' ...
'xmlns:batch=''http://schemas.google.com/gdata/batch'' ' ...
'xmlns:gs=''http://schemas.google.com/spreadsheets/2006''>' ...
strcat('<id>https://spreadsheets.google.com/feeds/cells/',spreadSheetKey,'/',workSheetKey,'/private/full/</id>')];
for i=1:nEntries
editKey=xmlData.getElementsByTagName('entry').item(i-1).getElementsByTagName('link').item(1).getAttribute('href').toCharArray';
slashIdx = strfind(editKey,'/');
editKey=editKey((max(slashIdx)+1):end);

event = [event '<entry>' ...
strcat('<batch:id>R',num2str(row),'C',num2str(columnNumbers(i)),'</batch:id>')...
'<batch:operation type=''update''/>'...
strcat('<id>https://spreadsheets.google.com/feeds/cells/',spreadSheetKey,'/',workSheetKey,'/private/full/R',num2str(row),'C',num2str(columnNumbers(i)),'</id>')...
'<link rel=''edit'' type=''application/atom+xml'' '...
strcat(' href=''https://spreadsheets.google.com/feeds/cells/',spreadSheetKey,'/',workSheetKey,'/private/full/R',num2str(row),'C',num2str(columnNumbers(i)),'/',editKey,'''/>')...
strcat('<gs:cell row=''',num2str(row),''' col=''',num2str(columnNumbers(i)),''' inputValue=''',cellValues{i},'''/>')...
'</entry>'];
end
event = [event '</feed>'];
ps = PrintStream(con.getOutputStream());
ps.print(event);
ps.close(); clear ps;
if (con.getResponseCode()~=200)
con.disconnect();
continue;
end
success=true;
end
if success
con.disconnect(); clear con;
else
display(['Last response was: ' num2str(con.getResponseCode) '/' con.getResponseMessage().toCharArray()']);
clear con;
return;
end

Scott

Never mind on my earlier post - I just needed to enable the drive API and the permission error disappeared. Everything works great now. Thank you!

Scott

I am getting "Last response (POST) was: 403/Forbidden", though I have entered my cliend id and secret, and also given the permissions for OAuth2. Are there other permissions Im missing?

Same here: it used to work perfectly, and it is so well done! Unfortunately it recently stopped working and I keep getting "Could not obtain authorization tokens from Google."
Something must have changed in Google's API. If anyone finds a fix, please post it.
Thanks

Has worked brilliantly until recently, when can not obtain tokens from Google any more. Is there a fix of any nature?

Great idea, but "MatlabGoogTest" returns "Could not obtain authorization tokens from Google." when I try to log in. I am eager to see a fix!

Sven

Wonderful work. The two things stopping 5 stars at the moment is documentation (none of the functions that the user must call have any help contents) and the 1-cell-at-a-time functionality only.

It would be very advantageous to supply similar functionality to xlsread/xlswrite, which both accept a cell range to read/write, and if no range is given, xlsread simply returns the full contents of that worksheet.

Malte Ahm

Really great functions! what is missing for me is a "editWorksheetCellBatch" function to be able to edit larger worksheets quicker.

I can see, that the Google API should support it:

https://developers.google.com/google-apps/spreadsheets/#updating_multiple_cells_with_a_batch_request

I have tried making the function myself, but my experience with java and html protocols is very limited. I keep getting an error 400 (Bad Request) when i try to send the data to the server (ps.print(event)).

Thomas

How long is the authentication valid for? I'm running this in a loop and I start getting 403 errors after about 500 seconds, which doesn't happen if I run each iteration by itself

Thomas

Thomas

Works great!

Matt,

The code below should work (replace connectNAuthorize function in the package with the one below)
urlread should be provided by Matlab.

The 'writely' token is for Google Spreadsheets, the 'wise' token is for Google Docs.

Once you have the tokens you can use the other functions in the package.

Hope this helps,
Claudiu
%%%%%%%%%%%%%%%%%%%%%%
userName='...@gmail.com';
password='...';

[authorized, aTokenS]=connectNAuthorize(userName, password,'writely');
if (~authorized)
result=-1;
return;
end
[authorized, aToken]=connectNAuthorize(userName, password,'wise');
if (~authorized)
result=-1;
return;
end

function [authorized, aToken]=connectNAuthorize(userName, password,service)

[str,authorized]=urlread('https://www.google.com/accounts/ClientLogin','POST',...
{'Email',userName,'Passwd',password,'source','My-Matlab-1','service',service});

if authorized==1
aToken=['auth' str(strfind(str,'Auth')+4:end-1)];
else
aToken='';
end

Matt Behr

hello .... is there anyway to avoid the credentials dialog. I want to run a script nightly to update data in google docs.

Thanks!
Matt

pangyuteng

Excellent toolbox! You can also easily modify the 'editWorksheetCell' to edit for an array of cells instead of individual cells for reducing the number of http requests [ http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#ListFeeds ].

Jake Hughey

Jake,

Thanks for the suggestion. I've updated the submission. You can now read both the cell value and its formula.

Hope this helps.

Jake Hughey

this set of functions looks great, but what is missing for me is a "getWorksheetCell" function, because I also want to pull data from google spreadsheets into matlab. I don't know java well enough to write one myself.

Updates

1.3.0.0

mention about new Google API

1.3.0.0

added two new functions
-editWorksheetRow
-editWorksheetColumn

1.2.0.0

Updated to Google OAuth2

1.1.0.0

Google login process has dialog box. New function reads values and formulas from the Google spreadsheet.

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

Discover Live Editor

Create scripts with code, output, and formatted text in a single executable document.


Learn About Live Editor