How To Do A Lookup to a Google Sheet
I was looking through the help articles and community posts, but couldn't find anything related to how to do a lookup to a google sheet. I just need something very basic (such as) "if value in column A matches {{record.id}}, give me the corresponding value in column B."
Any examples or reference documentation for this, or is this more complicated than it appears on the surface?
-
Dave Guderian this is a tricky one for sure. From everything I found, there are a few ways to do this and none of them are super easy. I very loosely made samples here that you can download and review.
First option:
This first option is the most simple, but seems a bit inefficient to me, especially if your google sheet has a lot of data on it. This option is to have a lookup step on your IO flow that gets all values on the sheet each time and then once you have the returned values, you do a postResponseMap script to find just the values you need/want. To make this method more efficient, you should just do the lookup once for page of data. In order to make a lookup run once per page, you need a preSavePage script on your export or a postResponseMap script on a step prior to the lookup (and it can't be on a lookup step immediately after a branch). The script will basically add in the record index to each record on the page, then you would put an input filter on lookup so that it only runs for the first record. After it runs for that record, you then use a postResponseMap script to take the results mapping put into record 1 and do what you need.
function preSavePage (options) {
for (let [i,d] of options.data.entries()) {
d.recordIndex = i;
d.pageIndex = options.pageIndex;
}
return {
data: options.data,
errors: options.errors,
abort: false,
newErrorsAndRetryData: []
}
}function postResponseMap (options) {
let lookupResults = JSON.stringify(options.postResponseMapData[0].lookupResults);
delete options.postResponseMapData[0].lookupResults;
//use the lookup results to do whatever you need.
for (let d of options.postResponseMapData) {
}
return options.postResponseMapData;
}
Second option:
This option would be similar to the first option and would depend how often the data in your Google spreadsheet gets updated. If it's constantly being updated, then this wouldn't be the best option. If your sheet is pretty static, then it would work well. In this option, you would follow a similar pattern to the HubSpot and Salesforce scenarios you've already dealt with. You have 1 flow that runs to grab all the lookup data you need, then writes that data to settings fields on the flow that needs access to it or writes it to the state api. For either way, you'd then need a script on your flow to go through the lookup table and output whatever you're needing.
Third option:
This option is the most complex, but it gives you the closest to what you're looking for. This option makes a Google Apps Script that we call to perform the lookup for us. You first need to follow the instructions under "How to retrieve the Google Drive iClient pair & specify callback URL" within this doc. This will guide you through making an OAuth client in Google. For our scenario here, you will need to enable the google drive api and the google apps scripts api for your oauth app.
First go to https://www.google.com/script/start/, then "start scripting", then "new project", then add a service for Google sheets, then paste in the code below.
function doGet(e = {
"parameter": {
"value": "12",
"spreadsheetId": "1ZEu1GG5988g6nW9tUDEKbCso0p0XCEctL7MkKBK15dA",
"column": "B",
"headerStatus": true
}
}) {
var findText = e.parameter.value;
var spreadsheetId = e.parameter.spreadsheetId;
var column = e.parameter.column;
var ss = SpreadsheetApp.openById(spreadsheetId);
var ranges = ss.createTextFinder(findText).findAll();
var headerStatus = e.parameter.headerStatus;
if (headerStatus == true || headerStatus == "true") {
var header = ss.getRange("A1:Z1").getValues()[0];
}
var res = ranges.map(r => ({
row: r.getRow(),
value: r.getSheet().getRange(`A${r.getRow()}:Z${r.getRow()}`).getValues()[0]
}));
var results = [];
var allColumn = {
"A": 0,
"B": 1,
"C": 2,
"D": 3,
"E": 4,
"F": 5,
"G": 6,
"H": 7,
"I": 8,
"J": 9,
"K": 10,
"L": 11,
"M": 12,
"N": 13,
"O": 14,
"P": 15,
"Q": 16,
"R": 17,
"S": 18,
"T": 19,
"U": 20,
"V": 21,
"W": 22,
"X": 23,
"Y": 24,
"Z": 25
};
var columnIndex = allColumn[column];
for (var key in res) {
var getRow = res[key]["row"];
var getValue = res[key]["value"];
if (getValue[columnIndex].toString().toLowerCase() == findText.toLowerCase()) {
if (headerStatus == true || headerStatus == "true") {
var headerValueObj = {};
for (var i = 0; i < header.length; i++) {
if (header[i] !== "") {
var headerName = header[i];
headerValueObj[headerName] = getValue[i];
}
}
results.push({
rowNumber: getRow,
row: headerValueObj
})
} else {
results.push({
rowNumber: getRow,
row: getValue
})
}
}
}
// Convert your results array to a JSON string
var jsonString = JSON.stringify(results);
// Return a TextOutput object created from the JSON string
return ContentService.createTextOutput(jsonString)
.setMimeType(ContentService.MimeType.JSON);
}Next, go to the settings wheel on the left and change the GCP project to the project you made in the first step for your oauth app. After that, deploy the script, click the settings wheel and select web app, select executing as user accessing the web, select who has access as Anyone with XYZ company. After deploying, copy the web app url.
Next, create your connection to google scripts in Celigo with the below setup:
https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive
Lastly, setup your flow to have a lookup with that connection, copied web app url, and add in the necessary handlebars to your url.
0 -
Tyler- I haven't had a chance to dig into this yet, but did want to take a second and thanks for the time you put into this. I should have some time next week to do some testing and will circle back if I run into issues.
Thanks again for providing this!
0
Please sign in to leave a comment.
Comments
2 comments