File Exchange

image thumbnail

GetGoogleSpreadshee​t

version 1.0.0.0 (1.97 KB) by Daniel
Download a google spreadsheet as csv and import into a Matlab cell array.

21 Downloads

Updated 02 Jan 2016

View License

result = GetGoogleSpreadsheet(DOCID)
[DOCID] A value like '0AmQ013fj5234gSXFAWLK1REgwRW02hsd3c', which is found in your spreadsheet's url: https://docs.google.com/spreadsheets/d/<here>/edit#gid=0.

[result] cell array of the the values in the spreadsheet

IMPORTANT: The spreadsheet must be shared with the "anyone with the link" option.

This has no error handling and has not been extensively tested.
Please report issues in comments below.

Thanks to @MarkTomaszewski and @Constantine for noting the updated url format (the above description has been updated to reflect this).

Comments and Ratings (38)

This recently stopped working for me - I was getting HTML back. To fix, I found that google added a checkbox in advanced sharing, something to the effect of "disable downloading for public". Once I unchecked this, it worked again.

marcio lima

Steve's solution almost works. Try this instead:
function data = parseCsv(data)
% splits data into individual lines
data = split(data,sprintf('\r\n')); % sdv 2017-12-15
% data = textscan(data,'%s','whitespace','\n'); % this does not work in general: cannot process data that has single \n or \r within cells
if isempty(data{end}), % trim any empty last line % sdv 2017-12-15
data = data(1:end-1);
end;
data = data.';
for ii=1:length(data)
%for each line, split the string into its comma-delimited units
%the '%q' format deals with the "quoting" convention appropriately.
tmp = textscan(data{ii},'%q','delimiter',',');
data(ii,1:length(tmp{1})) = tmp{1};
end
end

Worked straight out of the box. A nice solution to a practical problem.

Mark Tseng

It worked perfectly, however, chinese words on my spreadsheet import into cell array incorrectly...

Works as a charm.

If you want a particular sheet, I found this edit in Daniel's file to be working:
csvURL = ['https://docs.google.com/spreadsheet/ccc?key=' DOCID '&gid=' GID '&output=csv&pref=2'];
Where GID can be found in the google spreadsheet url of the specific sheet and pass it into the function as an extra parameter.

Jan Siroky

I did not want to rate this app, however, I do not see how to undo this. So i put five stars in as the other users :)

i have an error of unexpected matlab expression in line 1
it can't read my DOCID which is = '1FysI4O-MLyE0dETK5LU-e_L4axfOXeQj5OwhQzkagmo'
any help urgently please

hxen

Opps. Every mind. Didn't have the doc shared! Silly wabbit.

hxen

Thanks to Nicholas for the recommendation. For some reason I was not able to unzip on a Mac but only on a PC. Not sure why that is... I was able to run the program however and now confused of the output. I used the result = GetGoogleSpreadsheet(DOCID) command for a spreadsheet that contained only numerical values in the cells of the spread sheet. Not every cell contained a value. However what I got returned were empty cells ( shown as [ ]) for everything except the first column that gave be character returns of HTML code, the first row (result{1,1}) being <!DOCTYPE html>. How do I get my numerical values out, say if I wanted to see the values along the 3rd column of the row ranges 2:10. I would have thought it would be results{2:10,3} but get back only [ ] for each cell even though I have values in the spreadsheet. What am I doing wrong? Thanks,

@hxen, try installing a third party archiving tool, I highly recommend "The Unarchiver". I am able to unzip 'GetGoogleSpreadsheet.zip' with that utility with no issue.

theunarchiver . com

hxen

Hmm... I am unable to unzip this download. I'm on a Mac and have tried downloading several times. It reads: "Unable to expland 'GetGoogleShreadsheet.zip' into 'Downloads'. (Error 1 - Operatio not permitted.)" Anyone else encountering this problem?

So, there is a little bug. In the present version, if the text within a cell of the Google sheet contains new lines, then they will be interpreted as new lines of the CSV description spreadsheet.

The fix is to replace the internal function parseCsv with this:

function data = parseCsv(data)
% splits data into individual lines
data = split(data,sprintf('\r\n')); % sdv 2017-12-15
% data = textscan(data,'%s','whitespace','\n'); % this does not work in general: cannot process data that has single \n or \r within cells
if isempty(data{end}), % trim any empty last line % sdv 2017-12-15
data = data(1:end-1);
end;

for ii=1:length(data)
%for each line, split the string into its comma-delimited units
%the '%q' format deals with the "quoting" convention appropriately.
tmp = textscan(data{ii},'%q','delimiter',',');
data(ii,1:length(tmp{1})) = tmp{1};
end

Best
Steve

Works like a charm!

Worked as required.

David Cagle

I am on a corporate gmail account... script will return HTML and cell array similar to what "Timothy OConnor" wrote....

I created a file on my personal gmail account and get perfect results....

Anyone know how to read data from a google file that is shared with everyone on my company account?

I made the sheet “visible to anyone” and put the 'DOCID' info as required. Unfortunately, I always get the following message: Error: Unexpected MATLAB expression. Anyone got the same issue and found a solution to it?

Cedric Tsui

Timothy, you need to make the sheet visible to anyone with the link.

+1 on corp account - script appears to be returning the HTML in a 1861x1265 cell array

In response to my comment below, Matlab seems to use it's own built in browser for web related tasks. I can sign in to Google with this browser, but I still get the html when trying to download the link.

If I use web(url) to open the link manually, I can log in to Google and it then asks me where to save the link, but after choosing a location it seems to enter an infinite wait loop in the browser...

For some reason when I try this i get an HTML result of the login screen for Google. I'm logged into google in both Chrome and IE.

I can't make the sheet shareable with anyone since it's part of a corporate Google account that doesn't allow sharing to the outside world. I have no trouble downloading the link with other software (e.g. JMP) when I'm logged into Google with my default browser.

Anybody have a fix for this?

Where do I get parseCsv (line 36)? I do not have this function on matlab and cannot find online either.

@ mpn17

Line 1 should read:
function result = GetGoogleSpreadsheet(DOCID, gid)

Line 20 should read:
csvURL = ['https://docs.google.com/spreadsheet/ccc?key=' DOCID '&output=csv&pref=2&gid=' gid];

Yau Benor's response has a mistake in the line 20 code with an erroneous space between the 2 and ampersand "...pref=2 &gid..."

mpn17

@ Yau Benor, I tried your change to alter the code for multiple sheets, but had no luck.

Tried to change line 20 to the following to match up with the new URL format, but it outputs a 594x44192 array that is too big to read.

csvURL = ['https://docs.google.com/spreadsheets/d/' DOCID '/edit#gid=' gid];

Any help greatly appreciated!

Yau Benor

To allow multiple sheets - change lines 1 and 20 in the script to:

1 function result = GetGoogleSpreadsheet(DOCID, gid)

20 csvURL = ['https://docs.google.com/spreadsheet/ccc?key=' DOCID '&output=csv&pref=2 &gid=' gid];

This would be perfect if it would support a multi-sheet spreadsheet using the "GID" in the URL.

Shaun

This script works well, just a note that adding new lines within the sheet cells results in incorrect parsing of the sheet.

Shaun

you sir have revolutionized my labnotes

Constantine

Hi, it took me a while to figure out this very useful script for transferring info from google sheets. The 'DOCID' info in the script didn't mention getting rid of the /edit#gid=0 and spreadsheets/d stuff... what you stated explicitly in the helpful response below. If you can update the comments in the script it would be nice! Thanks!

Thanks for a great script works fine. :-) Any ideas on how to also store a hyperlink associated with a cell text, though?

@Daniel,

Thank you very much! This works perfectly when the sheet is set up to be viewed by anyone with the link. Otherwise, the output contains a large cell array of html.

Is there any way to make this work for sheets that are set up without sharing?

Also, your description may benefit from edits according to the following...

@Adam,

When I access my sheet in browser I see a URL that looks like this:
https://docs.google.com/spreadsheets/d/<the text here is the doc id>/edit#gid=0

Hope this helps!

~Mark

Adam Danz

This looks incredibly useful.

Any updates now that google sheets has been merged with good drive and no longer contains the 'key=' part (replaced with /d/ but still doesn't work with this code) ?

Nagavenkat

worked perfectly
Thank you

Updates

1.0.0.0

minor reformat description

1.0.0.0

update description as suggested by commenters

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

Inspired: Matlab to Google Sheets (matlab2sheets)