File Exchange

image thumbnail

Matlab to Google Spreadsheets

version 1.3 (22 KB) by

Matlab functions to send data to Google Spreadsheets.

4.77778
12 Ratings

6 Downloads

Updated

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

valarmathi I

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

Scott (view profile)

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

Scott (view profile)

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

Scott (view profile)

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?

Kent Conover

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

Sven (view profile)

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

Thomas (view profile)

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 (view profile)

Thomas

Thomas (view profile)

Works great!

Eric Trautmann

Eric Trautmann (view profile)

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

ted p teng

ted p teng (view profile)

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

mention about new Google API

1.3

added two new functions
-editWorksheetRow
-editWorksheetColumn

1.2

Updated to Google OAuth2

1.1

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

MATLAB Release
MATLAB 8.5 (R2015a)

Download apps, toolboxes, and other File Exchange content using Add-On Explorer in MATLAB.

» Watch video