Use Pipedream to add data from Google Sheets to Grafbase

Pipedream is an integration platform for developers to connect APIs quickly. Use it to create workflows with data sources, app integrations, and code templates for faster automation. There are thousands of apps, triggers and actions to explore for more productive development.

In this guide, we will walk through how to create a GraphQL API by connecting a Google Sheet through Pipedream, to Grafbase. Any changes to the sheet will be added to your Grafbase backend.

You will need an account with Pipedream and Grafbase to follow along.

Using Google Sheets to track expenses in a GraphQL API is a simple workflow for recording data that gets stored in a GraphQL backend. This allows the data to be accessed for further calculations, or to be displayed directly from the custom GraphQL API.

Inside of your project root, run the following command to initialize a new GraphQL backend with the Grafbase CLI:

npx grafbase init

Then inside of the file that was created at grafbase/schema.graphql update it to contain the following schema:

type Expense @model {
  amount: Int!
}

Add additional files for your project, initialize Git, and deploy the project to Github. Follow along with this guide for deploying your backend.

Once the GitHub repository is deployed and added to Grafbase Dashboard, navigate to the project's main branch and test out a mutation in the playground

mutation {
  expenseCreate(input: { amount: 11 }) {
    expense {
      id
      amount
    }
  }
}

Next, navigate to your Google Sheets account and add a new spreadsheet to your drive and give it a name. This will be the data source that begins the workflow actions.

Then, go to your account's Pipedream workflows and create a New Workflow

Start by adding a trigger that will send an event any time new updates are made in the connected Google Sheet.

First time setting up will require Google Authorization. Connect the Drive, Spreadsheet, and Worksheet

Select a type of trigger for Google Sheets, in this case, all row or cell updates are captured.

When configured, the workflow trigger is listening for any new updates.

Now that is added, navigate to the Google Sheet connected to the Pipedream workflow and edit a cell on any row

The updated value can be viewed as a new event in the Pipedream workflow

Now, you're able to explore the event exports for context and data

Each new update will be used for a request to the backend. Next, add a step to the workflow by searching for the Grafbase app

Select an action, to use any Grafbase API in Node.js

Use Pipedream's templated axios call that is customizable. Add the Grafbase project's URL and API key during the set configuration

import { axios } from '@pipedream/platform'

export default defineComponent({
  props: {
    grafbase: {
      type: 'app',
      app: 'grafbase',
    },
    amount: {
      type: 'string',
      label: 'Amount',
      description: 'Expense to record',
    },
  },
  async run({ steps, $ }) {
    const data = {
      query: /* GraphQL */ `
        mutation AddNewExpense($amount: Int!) {
          expenseCreate(input: { amount: $amount }) {
            expense {
              id
              amount
            }
          }
        }
      `,
      variables: {
        amount: parseInt(this.amount, 10),
      },
    }
    return await axios($, {
      method: 'post',
      url: this.grafbase.$auth.url,
      headers: {
        'Content-Type': 'application/json',
        'x-api-key': this.grafbase.$auth.api_key,
      },
      data,
    })
  },
})

Deploy the application after testing successfully, and the workflow is ready to go!

Add to the Google Sheet and see the data in Grafbase soon after

Use the API to keep records and display information, access it with a GraphQL request for your next application.

That's it! Your Pipedream workflow is listening to changes in the Google Sheet, and updating the Grafbase backend automatically.

From here, a custom application interface can do further calculations and display data collected in Google Sheets.