import gsheet

[paulvonhippel]

Can Stata import data from Google Sheets yet? In 2019-2021, there was a thread about this and the answer seemed to be that you needed to manually export the gsheet to Excel first. That's not an ideal solution because it's not fully automated. For example, if the content of the gsheet changes and you don't know, then you're pulling from an outdated Excel copy.

Has there been any progress on this since 2021? This seems important because there's so much data in gsheet format....

[Daniel Schaefer]

This is actually entirely possible using the generic -import excel- command. You just need to give it a correctly formatted google link.

Start by creating a sharable link by clicking "share," then setting it to "anyone with the link" under "general access." Setting the link to "general access" simplifies things, but if you want you can also set up oauth, include your authorization token in the url, and continue to use the document in restricted mode. Copy the link and click done. You should have something like this on your clipboard:

https://docs.google.com/spreadsheets/d/1IJV9Z7hGZFpAU6ggg0DlEA47S165zSZhz5tRdcozTEY/edit?usp=sharing

Now edit the link by deleting edit et al. at the end. Next, add export after the last slash to get a link like this:

https://docs.google.com/spreadsheets/d/1IJV9Z7hGZFpAU6ggg0DlEA47S165zSZhz5tRdcozTEY/export

If you plug the link into a browser you should see a download automatically start for a .xlsx file. That let's you know you can use the link with -import excel-.

This line of code downloads the data for me in Stata 18. I'll leave the link to the test data sheet I set up for the live for now, but I'll probably take it down soon.

import excel using "https://docs.google.com/spreadsheets/d/1IJV9Z7hGZFpAU6ggg0DlEA47S165zSZhz5tRdcozTEY/export", firstrow clear

[Daniel Schaefer]

Setting up oauth is more complicated than I let on in #2. That's what you'd want to do, ultimately, for a fully featured command for this, but as someone who has set up oauth on several platforms it's a real pain to get working.