Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • State & Google Sheets/Google Drive

    I am trying to import data from Google Sheets or Google Drive (no preference/recommendations welcome) and I cannot get the code to work.
    The code I am using is:

    import excel using "https://drive.google.com/open?id=1YXoNuhLYYlGWWdbODEcSAJec4omujdeM", cellrange(A11:B102) firstrow

    This is test GDP data that I grabbed from FRED, I have attempted to modify the privacy settings, but nothing has worked yet.

    Thanks!

  • #2
    I can't speak to Google drive, perhaps someone else can, but for what it's worth FRED (Federal Reserve Economic Data) data is fairly easy to read in directly:

    Code:
    import delimited using "https://fred.stlouisfed.org/series/GDPC1/downloaddata/GDPC1.csv", clear
    Or more generally

    Code:
    local series = "GDPC1"
    import delimited using "https://fred.stlouisfed.org/series/`series'/downloaddata/`series'.csv", clear
    Last edited by Justin Niakamal; 04 May 2020, 10:57.

    Comment


    • #3
      Appreciate the quick reply Justin!
      I know how to import FRED data directly from FRED, but I'm more interested in importing data that is hosted on Google Drive or a personal site for later use.

      Thanks!

      Comment


      • #4
        I haven't tried this feature, but FWIW you're asking Stata to import an Excel spreadsheet that isn't actually an Excel spreadsheet, so it would be surprising if that worked...

        I think you might need to explicitly save the Google sheet as an Excel spreadsheet, then try to import the Excel spreadsheet into Stata (that should work in that you're just using Google drive as a network or internet drive like dropbox). Unfortunately, that's probably not going to let you dynamically change the Google sheet without also saving as an Excel spreadsheet before importing to Stata (which I'm guessing is what you're trying to do?).

        Anyway, this is probably more about Google sheets than Stata (unless Stata is meant to work with Google sheets, which I'm not aware of). But if you could get Google sheets to present itself as an Excel spreadsheet, I suspect Stata would be happy to import it, but that's a Google sheets question, not a Stata question, so I wouldn't expect much help from this list (but I could be wrong).

        Comment


        • #5
          The file is stored on Google Drive as an Excel file.
          But that is a good reason to not use Google Sheets

          Comment


          • #6
            Originally posted by John Eiler View Post
            . . . if you could get Google sheets to present itself as an Excel spreadsheet, I suspect Stata would be happy to import it, but that's a Google sheets question, not a Stata question, so I wouldn't expect much help from this list (but I could be wrong).
            Google Drive File Stream makes your documents accessible as if they were on your hard drive, so it might work for your purpose. It looks like you have to have administrator privileges to install it on your PC. https://support.google.com/drive/answer/7329379
            Please let us know how this works.
            David Radwin
            Senior Researcher, California Competes
            californiacompetes.org
            Pronouns: He/Him

            Comment


            • #7
              Google sheets are not really Excel sheets and as pointed out by John, you need to get Google to present them as Excel spreadsheets. The way to do this is to share the document and export it as an Excel file. Some time ago I wrote a program which works if the document is shared:
              Code:
              capture program drop gsheet
              program define gsheet
                  syntax anything , key(string) id(string)
                  
                  local url "https://docs.google.com/spreadsheets/d/`key'/export?gid=`id'&format=csv"
                  
                  copy "`url'" `anything', replace
                  
                  noi disp `"saved in `anything'"'
                  
                  import delim using `anything', clear
              end
              anything is the filepath and filename you want to save the file (feel free to change it so it is using a tempfile rather than saves it). The option key is the bit in the sharing URL behind "/d/". For example if "https://docs.google.com/spreadsheets/d/18l-T2xk03s8FEgNdbEP9gONlU6CvUSl6SlKJJpGfFKU/edit?usp=sharing" is the link for sharing, key is "18l-T2xk03s8FEgNdbEP9gONlU6CvUSl6SlKJJpGfFKU". ID is the spreadsheet id, I am not sure how to obtain this one, but a quick google search will help. To download the first sheet in the google sheets document above you type:

              Code:
              gsheet "C:\test.csv" , key("1YXoNuhLYYlGWWdbODEcSAJec4omujdeM") id("0")
              Hope that helps.

              Comment

              Working...
              X