HOW TO: Extract data from Google Sheets and get data rows into name/value pairs
Today's post is brought to you by Dan Claypool, our resident grillmaster and support agent extraordinaire!
In this scenario, you want to extract data from Google Sheets. The parsing is a little unusual, so this will show you how to transform your data from an array to name/value pairs.
1. Set up your Google Sheets connection, then configure your scopes. In this case, we'll be using both of the available scopes.
2. Authorize your connection, then go to your flow.
3. Open your Google Sheet. One of the main things you will need is the unique Sheet ID in the URL (highlighted), which is the pointer to your particular Google Sheet.
4. In your export, choose the connection you created, then click Next.
5. In the configuration page, leave Form view set to Google Sheets.
6. In the What would you like to export? section, choose spreadsheets.values for the API name. These are the values coming out of the cells in the Sheet that you specify.
7. Choose the GET operation.
8. From your Google Sheet URL, copy and paste the unique ID from above into the spreadsheetId field.
9. Specify the range of cells in the Google Sheet that you'd like to extract in the range field, using the format A2:B4. You can go over the range in case you plan to add more rows to your Sheet. If you only have one Sheet, you can use A2:B4, but if you have several sheets, use: Sheet2!A2:B4. If you've renamed this to something with spaces, use quotes around the Sheet name.
10. In our example, we'll be mapping item to value
11. In your Search parameters, make sure the ROWS majorDimension is selected.
12. Click Preview to view. in your HTTP response tab, you can see the values coming through as a values array, and each data row as a nested array within values.
13. On your Export, click the Transform icon.
14. In the Transform record modal, you want to get to the first value in the values array, so in the Transform rules first column, you'll specify values.*.0 for the first column of the spreadsheet and values.*.1 for the second column of the sheet. For more columns, it would be values.*.2 and so on. Remember to start with 0.
15. In the second column of Transform rules, enter *.item for the first row and *.value for the second. You can change these item and value names to whatever you want. If you don't include the *., you'll only get the last row of data.
16. Now you'll have an array of item/value pairs that we'll be using in mapping next.
17. Save then open your import.
18. In your import, make sure that you have One to many set to Yes (Advanced). If it's set to No, this will just send the array as one big object, but we want to send the item/value pairs that are nested together.
19. Don't add a Path to many since there's no path defined above the array.
20. Choose CSV as File type, then specify the structure of your CSV file. If your file doesn't include a header and you're using our beta UI, there is a bug we're fixing where the Include header still shows as checked even if you uncheck it. It's actually set correctly to false in the JSON (and you can also check this in our classic UI, which renders this state correctly).
21. Click the mapping icon to specify the following mapping for your import. If you're using our beta, the values don't yet show as drop-down items which is something we're fixing. You can either type then in directly or switch to our classic UI to handle this step. If you do switch, since Google Sheets isn't supported in our classic UI, it will switch to the REST Form View.
22. Here's a test (Dan is using Raspberry Pi) to show that this now gets the name/value pairs as comma-separated values. 
Let us know if you have more Google Sheets tips or other great integrating tips!
Comments
We published a brand new template in our marketplace that makes it super easy to work with Google Sheets. We published a new article too explaining all the details (here). Hope this helps.
Another way to do this and not hardcode your field names is to utilize this script. If you select row A when getting sheet data, you can have a pre save page script to transform the data and create the JSON fields using the first returned result.
function preSavePage (options) {
let output = [];
for (let d of options.data) {
let fields = d.values[0];
for (let [index,r] of d.values.entries()) {
if (index > 0) {
let o = {};
for (let [indexF,v] of r.entries()) {
o[fields[indexF]] = v;
}
output.push(o);
}
}
}
return {
data: output,
errors: options.errors,
abort: false,
newErrorsAndRetryData: []
}
}
Please sign in to leave a comment.