Send results of Pipedream SQL query to Google Sheets
•@dylburger•
•code:
•data:private•last updated:4 years ago
Build integrations remarkably fast!
You're viewing a public workflow template.
Sign up to customize, add steps, modify code and more.
Join 800,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.
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]]
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}
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)