can i use data services to connect to a google sheet ?

  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a spreadsheet i want to process and create records from. What i need to create is not a one to one mapping so data loader won't work. But if i could attach to it as external data i would write js to do the processing.
Photo of ktyler

ktyler

  • 9,244 Points 5k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Official Response
Yes this is doable with Google's Spreadsheets API, which is REST-based.

To connect to a Google spreadsheet that is public and published to the web is pretty easy --- it's a little more work if you want to connect to a privateGoogle spreadsheet only accessible to you. In the private case you need to first create an OAuth 2.0 Authentication Provider in Skuid. Then you need to configure your Model Service to use this Authentication Provider. From there the setup is the same as in the public scenario.

Here I'll just document the public, published to web scenario.

First step: if your Google Spreadsheet is not published to the web, it needs to be --- here's how to do this. From your Google Spreadsheet, go to File > Publish to the Web, and click on the big "Publish" button or "Start Publishing". Now, grab the Spreadsheet Id out of the URL --- you'll need it in a bit. It's the long bit between /d/ and /edit, in a Google doc URL looking something like https://docs.google.com/spreadsheets/d/<b>SPREADSHEET_ID</b>/edit#gid=0


Next: back in Skuid, create a new Model Service called "GoogleSpreadsheets", using the REST Adapter, with URL https://spreadsheets.google.com, with all defaults checked.



Expand the "Common URL Parameters" section and click to open the action menu, then click the Append button to add a new URL Parameter. Enter "alt" for the key, and "json" for the value. Save, and click OK to add a Remote Site Setting when requested.





Now you're ready to create your REST Model! 

Go to the Skuid Page where you want to use your spreadsheet data, and click to add a new Model. For my sample I was storing Essential Planetary Data in a spreadsheet, so I called my Model PlanetaryData. For Data Source Type, choose REST, and Data Source choose GoogleSpreadsheets or whatever you named your Model Service. Then for Service URL, enter the following:

/feeds/list/SPREADSHEET_ID/od6/public/full

(Important: replace SPREADSHEET_IDwith your actual Spreadsheet Id!)




Now, click on "Path to Contents". This lets you source or rebase your Model onto a specific portion of the JSON data that is returned by Google's Spreadsheet API from this URL. Put another way, this URL gives you back lots of data, but for the sake of your Skuid work, all you care about is the Array of rows in your spreadsheet --- which will become Model rows in your REST Model --- and the cell values, which become field values for each column / field in the spreadsheet. So "Path to Contents" is telling Skuid how to navigate down to the portion of the big blob that Google gives you that you actually care about using in this Skuid page.




Next, go pick Fields that you want to use in your Model. Click on Fields and this should bring up the list of possible fields. For each Column in your Google Spreadsheet named "Column Name", there should be a field of type OBJECT available to be picked named Gsx$columnname, where your Spreadsheet column name has had its spaces removed and has been converted to lowercase. In my case I had 3 Columns: Planet, Number of Moons, Mass in Kilograms, and I got 3 Fields to choose from: Gsx$planet, Gsx$numberofmoons, Gsx$massinkilograms. You'll want to click the arrow to navigate intothese "object" fields, and select the $t field within the object. Kind of weird syntax, but that's what you have to do to get to the actual data values for the cells for each row.





Pick these fields and add them to your Model. If you click on the fields individually you can change their Labels and other metadata properties, which is helpful so that in your components the fields' labels are more human readable.



Then, drag your fields into your Field Editor / Table, or use the data in a Chart or Action Framework sequence or JavaScript snippet --- whatever you want to do!






Digging Deeper

This example uses Google Spreadsheets' "List Feed API". Here are the docs on managing list and cell-based feeds, and some of the issues to be aware of (such as "if you change your header row, or if any of your rows are blank, you'll have problems!"

https://developers.google.com/google-apps/spreadsheets/data

And here's the main Google Spreadsheets API doc, which lists out all available Spreadsheet API capabilities:

https://developers.google.com/google-apps/spreadsheets/?hl=en
(Edited)