Use Pipedream to add data from Google Sheets to Grafbase

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
    }
  }
}

Mutation Return Values

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. Add a New Google Sheet

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

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

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

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

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

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

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

Now, you're able to explore the event exports for context and data View Export from Successful Event

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 Grafbase Playground Query Expense Collection

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

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! Test and Deploy Workflow

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

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

Grafbase Playground Query Expense Collection

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.

Get Started

Create your backend to get started with Pipedream.