Google Drive - Find Rows
•@sergio•
•code:
•data:private•last updated:4 years ago
today
Build integrations remarkably fast!
You're viewing a public workflow template.
Sign up to customize, add steps, modify code and more.
Join 1,000,000+ developers using the Pipedream platform
steps.
trigger
HTTP API
Deploy to generate unique URL
This workflow runs on Pipedream's servers and is triggered by HTTP / Webhook requests.
steps.
google_sheets_find_rows
Given a sheet, column name, and column value, return the row number(s) if a row with that value is found, else return undefined.
auth
(auths.google_sheets)
params
SpreadsheetId
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
string ·params.spreadsheetId
Sheet name
The A1 notation of the values to look in for value. E.g., A1:E5 or Sheet1!A1:E5
string ·params.sheet_name
Column name
The A1 notation column name to look in for value, for example: A or B
string ·params.column_name
Column value
The column value to look for.
string ·params.column_value
code
async (params, auths) => {1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
}56
// 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;
};
// Pull 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.
const row_numbers = [];
let row_num = 0;
for (const row of sheet_values) {
if (String(row[selected_col]) === params.column_value) {
row_numbers.push(row_num);
}
row_num++;
}
if (row_numbers.length > 0) {
return row_numbers;
} else {
return undefined;
}