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

Does your spreadsheet provider have a REST API? 

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/SPREADSHEET_ID/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_ID with 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 _into_these “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

Zach, this is great!

1. I have a custom object in Salesforce. I need to get data from google spreadsheets create one row in the custom object to match each row in the spreadsheet. The cell names match the column names in the spreadsheet. Do you have some sample code to create these custom object rows from the google spreadsheet data?

2. We actually need to do this process daily as the spreadsheet are financial credit card transaction that we are loading into our system. Do I need to create a new skuid page for every google spreadsheet, or is there a way to only have to input the Google Sheet Id without rebuilding the page each time?

I tried this, but get the error “2015&id=a9kU0000000CayVIAS:81 Uncaught TypeError: batch_transaction_model.createRow is not a function”

// Generate one “MMA Transfirst Batch Transaction” for each line of Google Data // console.log(params);
  
var Google_Sheet_Data=skuid.model.getModel(‘Googlesheettest’).data;
var GS_id = Google_Sheet_Data[0].Id;
console.log ( "  GS id  " + GS_id);
var GS_Detail_Number=Google_Sheet_Data[0].gsx$detail.$t;
var GS_Card_Number = Google_Sheet_Data[0].gsx$cardholdernumber.$t;
var GS_Date = Google_Sheet_Data[0].gsx$trandate.$t;
var GS_Amount = Google_Sheet_Data[0].gsx$tranamt.$t;
var batch_model=skuid.model.getModel(‘MMA_Transfirst_Batch’);
var batch_transaction_model=skuid.model.getModel(‘MMA_Transfirst_Batch_Transaction’).data;
var number_of_Google_Rows = Google_Sheet_Data.length;
for (i=0;i<number_of_Google_Rows;i++){
         console.log(i);
         console.log(’ GS_row ’ + Google_Sheet_Data[i].Id);
         var newRow = batch_transaction_model.createRow(),
         rowUpdates  ={Name:gsx$detail.$t,CC_number__c:gsx$cardholdernumber.$t,Transaction_Amount__c:gsx$tranamt.$t,MMA_Transfirst_Batch__c:param.Id};
         batch_transaction_model.updateRow(newRow,rowUpdates);
         
     }
batch_transaction_model.save();

Got it to work:

var Google_Sheet_Data=skuid.model.getModel(‘Googlesheettest’).data;var batch_model=skuid.model.getModel(‘MMA_Transfirst_Batch’);
var batch_transaction_model=skuid.model.getModel(‘MMA_Transfirst_Batch_Transaction’);
var number_of_Google_Rows = Google_Sheet_Data.length;
var rows_to_process = number_of_Google_Rows-1
// we do not process the last row because it is the total
for (i=0;i<rows_to_process;i++){
         console.log(i);
         console.log(’ GS_row ’ + Google_Sheet_Data[i].Id);
         var GS_id = Google_Sheet_Data[i].Id;
         console.log ( "  GS id  " + GS_id);
         var GS_Detail_Number=Google_Sheet_Data[i].gsx$detail.$t;
         console.log ("  Number  " + GS_Detail_Number);
         var GS_Card_Number = Google_Sheet_Data[i].gsx$cardholdernumber.$t;
         var Last_Four_of_GS_Card_Number = “xxxx xxxx xxxx " + GS_Card_Number.slice(12);
         console.log (” GS Card Number  " + GS_Card_Number);
         var GS_Date = Google_Sheet_Data[i].gsx$trandate.$t;
         console.log (" GS Date  " + GS_Date);
         var GS_Amount = Google_Sheet_Data[i].gsx$tranamt.$t;
         console.log (" GS Amt  " + GS_Amount);
         var newRow = batch_transaction_model.createRow(),
         rowUpdates = {Name:GS_Detail_Number,CC_number__c:Last_Four_of_GS_Card_Number,Transaction_Amount__c:GS_Amount.slice(1)};
         batch_transaction_model.updateRow(newRow,rowUpdates);
         
     }
batch_transaction_model.save();

This is a great example of the endless possibilities available with REST data sources.
I don’t work for them, but G-Connector (sheets app link, salesforce app link) could make things interesting. G-Connector allows you to retrieve Salesforce data on a scheduled basis and manipulate it in Google spreadsheets. You can apply the power of pivot tables and the flexibility of spreadsheet formulas directly to your Salesforce data. Here comes skuid to pump that power directly back into Salesforce! Can’t wait for a use case to try this out!

Hi all,

I think this post is pretty dated; can someone help me get this working on Skuid Platform with Google v4 API? I have a get URL that works outside of Skuid, so I think the file is correctly accessible. However when I put in the relative data source URL it fails, and the error message seems to indicate Skuid prefixes the relative URL.

I tried the v3 format in this thread in Skuid:

/feeds/list/(sheeetID)/od6/public/full

When I click “path to contents” I get this error:

Loading sample body for REST Data Source…
Error connecting to REST Data Source at URL “/api/v1/feeds/list/(sheetID/od6/public/full”: Not Found: Page Not Found Whoops! Our bad. This link is broken or the page has been removed. Click your browser’s back button to return to the previous page.


The v4 syntax that works in the Google API explorer is:

GET https://sheets.googleapis.com/v4/spreadsheets/(sheetID)


So I tried to put in the v4 syntax (starting with the /v4) but the same error occurs, with the same /api/v1 tacked on to the front.

So, do i have the URL wrong? Or is there some configuration that will prevent Skuid from putting that prefix on?

Thanks for your help

I am also having this issue

Ok, Skuid friends, time to share the secret.

At Dreamforce I saw your kiosk signup sheet and was told it was saving to Sheets. I heard that a) it was not trivial to set up, but b) ultimately it was just a matter of getting the configuration right (i.e. no code hack required).

So, can we get the magic config from your engineer, please?

Make the google sheet columns have the exact same names as the fields that you are trying to match. Make one more column in the sheet for the Salesforce ID of the object that you are mapping to. Then, it should work. 

Thanks for the input Anna, but I think my problem is before that. I’m on the step trying to create a model that will link to the sheet. I get an error when Skuid attempts that connection, so I don’t even have a chance to work at the field level. And I’m doing this on Skuid platform; no Salesforce involved.

Zach’s directions indicate: For Data Source Type, choose REST, and Data Source choose GoogleSpreadsheets or whatever you named your Model Service. 

In my version of Skuid Platform, if I select the REST Data Source Type, the Data Source is fixed at “SkuidLocal” so I cannot change it to be the data source I created. If I select a Data Source Type of “Google Drive” then I CAN select the data source I created, but this data source type does not have the Service URL field so I can’t enter my URL. Also, the terminology in platform is now “data source” instead of Zach’s “model service”.

All of this leads me to believe Zach’s instructions are just outdated, and is why I hoped someone could provide updated instructions. Since I know Skuid got it to work, SOMEBODY over there knows how to do it.

Any help appreciated!
-John

Oh! I’m getting closer. I made multiple data sources with different configurations and can pick them now on the REST type. A different error message now; I may get this after all…

Nope, still stuck. I’ve tried a no-auth and an auth data source and get two different errors:

Error connecting to REST Data Source at URL “https://spreadsheets.google.com/feeds/list/(sheetID)/od6/public/full”: DriveSorry, unable to open the file at this time. Please check the address and try again. Get stuff done with Google DriveApps in Google Drive make it easy to create, store and share online documents, spreadsheets, presentations and more.Learn more at Personal Cloud Storage & File Sharing Platform - Google.


Error connecting to REST Data Source at URL “https://spreadsheets.google.com/feeds/list/(sheetID)/od6/public/full”: Unable to authenticate to data source “REST-drive-auth”: Login has been cancelled

It’s highly likely I made an error configuring authentication, so I’d prefer to keep hammering on the no-auth option, which should be fine since the sheet is published on the web and can be retrieved using Postman and opening in anonymous browsers.

Would love any tips.

-John