Google sheets - format array of objects into array of arrays
@dylburger
code:
data:privatelast updated:10 months ago
today
Build integrations remarkably fast!
You're viewing a public workflow template.
Sign up to customize, add steps, modify code and more.
Join 200,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 theauths object
code
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 invocations 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 theauths object
code
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 invocations 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.

1mweDONSnGMtVV1j1-X3NXnkMMnre9IxlTwq8v-rys2E
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.

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