Send results of Pipedream SQL query to Google Sheets
@dylburger
code:
data:privatelast updated:1 year 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
Cron Scheduler
Deploy to configure a custom schedule
This workflow runs on Pipedream's servers and is triggered on a custom schedule.
steps.
query
Run a query against data saved to Pipedream's SQL service. To get started, connect your account using your API key (found in your account settings).
auth
(auths.pipedream)
params
Query

The SQL query to run.

SELECT * FROM my_table_name
string ·params.query
Optional
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
const axios = require('axios')

const response = await axios({
  url: `https://rt.pipedream.com/sql`,
  method: 'POST',
  headers: {
    Authorization: `Bearer ${auths.pipedream.api_key}`,
  },
  data: {"query": params.query}
})

if (response.data.error.toDisplay) {
  throw(response.data.error.toDisplay)
} else {
  const rv = {}
  const metadata = {}
  rv.columnInfo = response.data.resultSet.ResultSetMetadata.ColumnInfo
  rv.queryExecutionId = response.data.queryExecutionId
  rv.csvLocation = `https://rt.pipedream.com/sql/csv/${response.data.resultsFilename}`

  let resultSet = []

  if (params.returnFormat === "object") {
    let headers = response.data.resultSet.Rows.shift()
    for (let j = 0; j < response.data.resultSet.Rows.length; j++) {
      let row = response.data.resultSet.Rows[j]
      let obj = {}
      for (let i = 0; i < row.Data.length; i++) {
        obj[headers.Data[i].VarCharValue] = row.Data[i].VarCharValue
      }
      resultSet.push(obj)
    }
  } else {
    response.data.resultSet.Rows.forEach(row => {
      let map = row.Data.map(data => data.VarCharValue)
      resultSet.push(map)
    })
  }

  
  rv.results = resultSet
  //rv.metadata = metadata

  return rv
}
steps.
add_new_rows_to_sheet
Add multiple rows to a sheet using the append API: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
auth
(auths.google_sheets)
params
Rows

An array of arrays representing the rows, for example [[1, 2, 3], [4, 5, 6]]

{{steps.query.$return_value.results}}
array ·params.rows
Spreadsheet ID

The ID of the target spreadsheet, found at the end of the URL: https://docs.google.com/spreadsheets/d/{id}

123
string ·params.spreadsheetId
Sheet Name

The sheet you'd like to add a row to

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
const data = {
  values: params.rows,
}
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,
}
return await require("@pipedreamhq/platform").axios(this, config)