Google sheets - format array of objects into array of arrays
•@dylburger•
•code:
•data:private•last updated:3 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.
sample_data
auth
to use OAuth tokens and API keys in code via the
auths objectcode
Write any Node.jscodeand use anynpm package. You can alsoexport datafor use in later steps via
return or this.key = 'value', pass input data to your code viaparams, and maintain state across executions with$checkpoint.async (event, steps) => {1
2
3
4
5
6
7
8
9
}10
// This is sample data we'll add to Google Sheets
// The data will be available in the variable steps.sample_data.$return_value
return {
"data": [
{ "name": "Luke", "title": "Jedi" },
{ "name": "Leia", "title": "General" }
]
}steps.
format_data
auth
to use OAuth tokens and API keys in code via the
auths objectcode
Write any Node.jscodeand use anynpm package. You can alsoexport datafor use in later steps via
return or this.key = 'value', pass input data to your code viaparams, and maintain state across executions with$checkpoint.async (event, steps) => {1
2
3
4
}5
// For each element in the `data` array above,
// create a new array with the name as the first element, and the title as the second
return steps.sample_data.$return_value.data.map(character => [character.name, character.title])steps.
add_multiple_rows_to_sheet
Add multiple rows of data to Google Sheets
auth
(auths.google_sheets)
params
Rows
Enter individual cell values below, or turn structured mode off to pass an array of arrays representing multiple rows of data enclosed in {{...}} — e.g., {{event.arrayOfArrays}} or {{[[1,2,3],['a','b','c']]}}
- Each nested array represents a row of data
- The elements within each nested array represent the columns of the row
[0]:
array ·params.rows
Spreadsheet ID
The spreadsheet ID can be extracted from its URL. E.g., the spreadsheet ID in the URL docs.google.com/spreadsheets/d/abc1234567/edit#gid=0 is abc1234567.
string ·params.spreadsheetId
Sheet Name
Enter the name of the tab in the spreadsheet where you want to add data. The default name for new sheets is Sheet1.
string ·params.sheetName
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
const { rows } = params
// Validate data input -- check `rows` data is an array of arrays
let inputValidated = true
if (!rows || !rows.length || !Array.isArray(rows)) {
inputValidated = false
} else {
rows.forEach(row => { if(!Array.isArray(row)) { inputValidated = false } })
}
// Throw an error if input validation failed
if(!inputValidated) {
console.error("Data Submitted:")
console.error(rows)
throw new Error("Rows data is not an array of arrays. Please enter an array of arrays in the `Rows` parameter above. If you're trying to send a single rows to Google Sheets, search for the action to add a single row to Sheets or try modifying the code for this step.")
}
// Configure object to post data to Google Sheets
const config = {
method: "post",
url: `https://sheets.googleapis.com/v4/spreadsheets/${params.spreadsheetId}/values/${params.sheetName}:append`,
params: {
includeValuesInResponse: true,
valueInputOption: "USER_ENTERED"
},
headers: {
Authorization: `Bearer ${auths.google_sheets.oauth_access_token}`,
},
data: {
values: rows,
}
}
// Post data to Google Sheets and return transaction metadata
return (await require("@pipedreamhq/platform").axios(this, config)).updates