One can Import multiple sheets from an Excel file, say sheet "n" with Import["file.xls"][[n]],
but how to do that with a Google sheet? I only know how to import the first sheet.
$\begingroup$
$\endgroup$
1
-
1$\begingroup$ How are you importing from Google Sheets right now? $\endgroup$– Carl LangeCommented Oct 16, 2021 at 17:10
Add a comment
|
1 Answer
$\begingroup$
$\endgroup$
2
The following works for me.
Save the sheet to your google drive. Right-click, "Get Link" Make sure that "Anyone with link" setting is selected as opposed to "Restricted".
"Copy Link" to get the link. Paste it in a notebook cell and put quotes around it:
https : // docs . google . com/spreadsheets/d/ 1 kut8YLiPKMXFs_ 8 Zpt9quQUa0K7yq0pP4QEV_sM2umo/edit?usp = sharing
Get rid of the edit?usp=sharing and instead write: export?format=xlsx as shown for the fictitious link here.
url = URL[
"https://docs.google.com/spreadsheets/d/1kut8YLiPKMXFs_\
Zpt9quQUa0K7yq0pP4QEV_sM2umo/export?format=xlsx"]
data = Import[url]
In my example, there are two sheets with toy data:
{{{"a", 1.}, {"b", 2.}, {"c", 3.}, {"d", 4.}, {"e", 5.}, {"f", 6.}, {"g", 7.}, {"h", 8.}, {"i", 9.}, {"j", 10.}}, {{"one", 1.}, {"two", 2.}, {"three", 3.}, {"four", 4.}, {"five", 5.}, {"six", 6.}, {"seven", 7.}, {"eight", 8.}, {"nine", 9.}, {"ten", 10.}}}
that you can get as data[[1]] and data[[2]].
-
$\begingroup$ Thank you, Syed, this was exactly what I needed. I had tried "/export?format=xls" but had neglected to try the xlsx extension. (Otherwise I have been using "csv" for the single sheet documents.) You've been very helpful. $\endgroup$ Commented Oct 17, 2021 at 12:16
-
$\begingroup$ I am glad, I was able to help you. Please consider accepting the answer as it motivates future visitors to the page to actually read anything on the page. $\endgroup$– SyedCommented Oct 18, 2021 at 11:46