Adding Google Sheets to AGOL

[KirstinRochel]

I am building a Dashboard that relies on tabular data and that is located in a Google spreadsheet. Since the spreadsheet is dynamic and gets updated constantly, I would like those changes to be reflected in the Dashboard automatically and as soon as they occur. Thus, I am trying to add a Google Sheet to my ArcGIS Online content rather than a CSV file, but when I select the spreadsheet I always receive the following error message: "Drive file is invalid or not found"

These are the steps I take when I try to add the Google Sheet:

1. In the AGOL Content menu, I click on Add Item and choose From a Cloud Drive
2. I select Google Drive as my source.
3. I change the File Type to Google Sheets and select the spreadsheet from my drive. This is when the error message pops up.

I have tried a similar process in the Map Viewer but I still receive the same message that the file could not be found. I also published the Google Sheet to the web first but I still cannot load it into AGOL.

I would like to mention that some other Google Sheets in my Drive can be added without any issues but this one is giving me trouble. The Google Sheet in question includes several formulas (IMPORTRANGE, ARRAYFORMULA, etc) that allow me to access other spreadsheets to import records and a large number of records (over 9,000). Could this be the problem? Is there a limit on the number of records that can be added via Google Sheets? Are the formulas causing these issues for some reason?

[VanessaGoold]

Hi Kirstin Rochel, did you ever figure out what was going on with adding your Google Sheet to AGOL? I am having the same trouble. Other sheets can be added, except for the one I want. It has no formulas and I can't figure out anything unusual about it. Would love your insight. Thanks!

[Anonymous User]

Hey Kirstin,  is the error that you are receiving "Drive file is invalid or not found" or "Cloud drive file is invalid or not found.".  The reason I ask is because in the best there was a defect for the latter.  

It is possible that the formulas are causing the issue.  I spoke with a colleague of mine who ran into a similar problem.  Would it be possible for you to try uploading a subset of that Google Sheet that does not contain the formulas?  Do you still get the error then?  

[HollyTorpey]

@KirstinRochel I think you're right about a maximum record count. I'm having the same problem with a sheet that has over 7,000 records. If I change my formula to import only 500 records, I can add it with no problem. I have not yet been able to find documentation of what exactly that upper limit is, though.

[MEMAGIS__MEMA_]

I don't have a solution, but I have to ask: do you have a python code or program that can take the data, download it, then push it up to AGOL?  Google sheets is not an automated active entity in AGOL.  When you upload a google sheet to AGOL, it basically sits like a hosted table.  You have to manually "append" to update the layer any time changes occur (unless you have a code, like our IT dept uses for COVID daily updates).