Example 4: Get data from an API on a schedule and add to Google Sheets

Build integrations remarkably fast!

You're viewing a public workflow template.

Join 800,000+ developers using the Pipedream platform

Deploy to configure a custom schedule

This workflow runs on Pipedream's servers and is triggered on a custom schedule.

steps.

README

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 executions with$checkpoint.

async (event, steps) => {

}

/*  
This workflow uses two actions. The first action 
uses the Star Wars API to retrieve details for a movie;
it requires no authentication.

The second action inserts select data returned by the 
Star Wars API into Google Sheets. To use this action:

1. Connect your Google Sheets account
2. Create a new Google Sheet and add the ID (from the URL of your sheet)
3. Add the sheet name (typically "Sheet1" for new documents)

When you're done, deploy and run the workflow.
*/

steps.

get_film

A `film` resource is a single film.

auth

(auths.swapi)

params

Enter an ID to get a specific film resource.

string ·params.id

code

async (params, auths) => {

}

return await require("@pipedreamhq/platform").axios(this, {
  url: `https://swapi.dev/api/films/${params.id}`
})

steps.

add_single_row_to_sheet

Add a single row of data to Google Sheets

auth

(auths.google_sheets)

params

Columns

structured mode:

Enter the data to insert into each column. Click + to add columns in structured mode, or turn structured mode off to enter array of column values as an expression — e.g., {{[1,2,3]}}

[0]:

Episode {{steps.get_film.$return_value.episode_id}}

[1]:

{{steps.get_film.$return_value.title}}

[2]:

Directed by {{steps.get_film.$return_value.director}}

[3]:

Produced by {{steps.get_film.$return_value.producer}}

array ·params.columns

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 a new sheet is Sheet1.

Sheet1

string ·params.sheetName

code

async (params, auths) => {

}

const { columns } = params

// validate input
if (!columns || !columns.length) {
  throw new Error("Please enter an array of elements in the `Columns` parameter above")
} else if (!Array.isArray(columns)) {
  throw new Error("Column data is not an array. Please enter an array of elements in the `Columns` parameter above.")
} else if (Array.isArray(columns[0])) {
  throw new Error("Column data is a multi-dimensional array. A one-dimensional is expected. If you're trying to send multiple rows to Google Sheets, search for the action to add multiple rows to Sheets, or try modifying the code for this step.")
}

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: [columns],
  }
}
return (await require("@pipedreamhq/platform").axios(this, config)).updates