The ID of the spreadsheet to look in for value. The spreadsheetID can be found in the URL when viewing your Google sheet. E.g., https://docs.google.com/spreadsheets/d/[spreadsheetId]/edit#gid=0
The A1 notation of the values to look in for value. E.g., A1:E5 or Sheet1!A1:E5
The A1 notation column name to look in for value.
The column value to look for.
async
(params, auths) => {
}
// Help function used to convert column name in A1 convention to 0 based index.
// Adjusted from @flambino 's answer in this codereview question: https://codereview.stackexchange.com/questions/90112/a1notation-conversion-to-row-column-index
function columnA1To0BasedIndex(colA1) {
var i, l, chr,
sum = 0,
A = "A".charCodeAt(0),
radix = "Z".charCodeAt(0) - A + 1;
if (typeof colA1 !== 'string' || !/^[A-Z]+$/.test(colA1)) {
throw new Error("Expected column label");
}
for (i = 0, l = colA1.length; i < l; i++) {
chr = colA1.charCodeAt(i);
sum = sum * radix + chr - A + 1
}
return sum - 1;
};
// Pulling sheets value from Google Sheets API Get sheet values endpoint.
const config = {
url: `https://sheets.googleapis.com/v4/spreadsheets/${params.spreadsheetId}/values/${params.sheet_name}`,
headers: {
Authorization: `Bearer ${auths.google_sheets.oauth_access_token}`,
},
}
const sheet_values = (await require("@pipedreamhq/platform").axios(this, config)).values;
// Gets the 0 index column based on the column name parameter.
var selected_col;
try {
selected_col = columnA1To0BasedIndex(params.column_name);
} catch (ex) {
console.log("Enter a column name in A1 format.");
return;
}
// Iterates rows in sheet values and compares with the value provided at the specified column.
for (const row of sheet_values) {
if (String(row[selected_col]) == params.column_value) {
return row;
}
}
return undefined;