Return data in Google Sheet as an array of JSON objects
@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.
get_values
Get values from Google Sheet using the Google APIs Node.js Client and the spreadsheets.values.get method: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get
auth
(auths.google_sheets)
params
SpreadhsheetId

The ID of the spreadsheet to insert rows into. 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.spreadhsheetId
Range

The A1 notation of the values to retrieve. E.g., A1:E5 or Sheet1!A1:E5

A1:Z1000
string ·params.range
code
async (params, auths) => {
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
}
16
const {google} = require('googleapis')

const auth = new google.auth.OAuth2()
auth.setCredentials({ access_token: auths.google_sheets.oauth_access_token })
const sheets = await google.sheets({version: 'v4', auth});

const response = await sheets.spreadsheets.values.get({
  spreadsheetId: params.spreadhsheetId,
  range: params.range
})

this.status = response.status
this.statusText = response.statusText
return response.data
steps.
format_json_and_return
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
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
}
28
// First row contains our headers / keys
const rows = steps.get_values.$return_value.values
this.headerRow = rows.shift() 

// Build our array of objects iteratively. 
// See the docs on step exports for the this.arrayOfObject syntax:
// https://docs.pipedream.com/workflows/steps/#step-exports
// That array will appear below the step - great for troubleshooting!
this.arrayOfObjects = []
for (const row of rows) {
  const obj = {}
  // Every row contains cells (columns) of data
  for (const [index, cell] of row.entries()) {
    obj[this.headerRow[index]] = cell
  }
  this.arrayOfObjects.push(obj)
}

// https://docs.pipedream.com/workflows/steps/triggers/#customizing-the-http-response
$respond({
  status: 200,
  headers: {
    "Content-Type": "application/json"
  },
  body: this.arrayOfObjects
})