Napkin Blog - How to Get Google Form Responses on Slack

If you need to create a form or quiz, the first thing you probably think of is Google Forms. Unfortunately, there’s no easy way to get notifications when someone actually submits your form. The only default notification method Google Forms provides is email; however, most people nowadays look to chat apps like Slack or Discord for up-to-date notifications.

Thankfully with Napkin, it’s easy to build a function that pings Slack or Discord whenever there’s a new form submission. In this post, we’ll show how to set up that function ourselves (you can also fork the JS version or the Python version). Read on for a step-by-step guide as well as how to set up the appropriate Google and Slack APIs.

Create A Google Form

First thing’s first, let’s make a Google Form. For this example, we made a simple form that asks the user for their ice cream preferences. You can see it here https://forms.gle/JxYCJN2NjpwhF8387. Feel free to copy this one as you do this tutorial.

Enable Google Sheets Sync For Your Form

We’ll have Google save the submission data in Google Sheets so we can access it from our Napkin function. To enable Google Sheets syncing for your form, go to the “Responses” tab of your Google Form. Click the green Google Sheets icon and then “Create”. Google will now save all form submission data to the spreadsheet that was just created.

Create A Google Cloud Project with Access to Google Forms

In order to get programmatic access to Google Forms and Sheets, we need to create a new Project in Google Cloud Console. Head to https://console.cloud.google.com/projectcreate and create a new Project.

Next we need to give our project access to Google Forms and Google Sheets.

Google Sheets Access

Visit here in the GCP dashboard and click Enable.

Google Forms Access

Same thing for Google Forms: enable that here.

The last thing we need to do here is create a set of credentials for our Napkin Function to access these resources on behalf of our Google Project. In Google Cloud, this is done by creating a Service Account, which is essentially a “bot” Google account that has access to our GCP Project and our Google Sheet. Once that’s created, we create API credentials for our Service Account and then use those credentials in our Napkin function.

Setting Up a Google Project Service Account

Go to https://console.cloud.google.com/iam-admin/serviceaccounts/create, give your Service Account a name, and then assign it the “Owner” role, then click “Done”.

Generate the Service Account’s API Key

From the Google Cloud dashboard, click the hamburger icon next to your new service account and then click “Manage Keys”.

Now click the “Add Key” dropdown, “Create new key”, and “Create” (make sure “JSON” is selected as the Key Type). A JSON file with your credentials will be automatically downloaded. We will be using this shortly.

Last thing we need to do is give our new service account access to our Google Sheet. Just copy the service account email from the GCP dashboard and then share your Google Sheet with that email (just as if it were a human account).

Create New Napkin Function

Head to https://napkin.io and create a new Napkin function. There are example functions you can fork for both Python and Javascript, but for this tutorial we’ll use Javascript.

First, add the google-spreadsheet and node-fetch modules to your function. This will make accessing the Google Sheets data easier.

Remember the JSON file that was saved when we created our service account key? Let’s upload it to Napkin so our function can use it to authenticate with Google. Go to the “Other” tab in the Napkin editor and drag the file into the Files section.

Finally, we'll add the ID of our Google Sheet as an env variable. Go to the Google Sheet, copy the ID from the URL and paste it into the Environment Variables section of Napkin's "Other" tab.

The Code

At a high level, our code will do the following:

Read the data from the Google Sheet.
Check if the latest submission has been reported yet by comparing the timestamp to the latest timestamp we have stored in the Napkin Key-Value store.
Notify Slack with the latest form data. Then, cache our latest submission timestamp in the Napkin Key-Value store for the next time the function runs.

You can copy the code below, or just fork the function here. See here for Python code

import { store } from "napkin"
import { GoogleSpreadsheet } from "google-spreadsheet"
import fetch from "node-fetch"
import fs from "fs"

const credsFilePath = "/opt/files/service_account.json"
const SlackWebhookURL = process.env.SLACK_WEBHOOK_URL
const isLaterThan = (t1, t2) => new Date(t1) > new Date(t2)

const objToBlocks = (obj) => {
  const blocks = {
    blocks: [
      {
        type: "header",
        text: {
          type: "plain_text",
          text: "New Form Submission :icecream:",
          emoji: true
        }
      }
    ]
  }

  Object.keys(obj).forEach((text) => {
    blocks.blocks.push({
      type: "section",
      text: {
        type: "mrkdwn",
        text: `*${text}*\n${obj[text]}`
      }
    })
  })

  // add a divider to improve readability
  blocks.blocks.push({
    type: "divider"
  })

  return blocks
}

const sendSlackMessage = async (blocks) => {
  await fetch(SlackWebhookURL, {
    headers: {
      Accept: "application/json",
      "Content-Type": "application/json"
    },
    method: "POST",
    body: JSON.stringify(blocks)
  })
}

export default async (req, res) => {
  const creds = await fs.promises
    .readFile(credsFilePath, "utf8")
    .then((data) => JSON.parse(data))
    .catch((err) => {
      console.info("There was an error")
      console.error(err)
    })

  const doc = new GoogleSpreadsheet(process.env.SHEET_ID)
  await doc.useServiceAccountAuth(creds)
  await doc.loadInfo()

  const sheet = doc.sheetsByIndex[0]
  const rows = await sheet.getRows()

  if (rows.length == 0) {
    console.info("No submissions yet. Exiting...")
    return
  }

  const latestTs = rows[rows.length - 1].Timestamp

  const allSubmissions = rows.map((r, i) => {
    return Object.fromEntries(
      sheet.headerValues.map((name, j) => {
        return [name, r[name]]
      })
    )
  })

  const storeKey = `lastCached-${process.env.SHEET_ID}`

  const { data } = await store.get(storeKey)

  if (!data || isLaterThan(latestTs, data)) {
    const newSubmissions = data
      ? allSubmissions.filter((x) => isLaterThan(x.Timestamp, data))
      : allSubmissions
    const messageBlocks = newSubmissions.map(objToBlocks)

    for (let blocks of messageBlocks) {
      await sendSlackMessage(blocks)
    }

    const maxTs = Math.max(...newSubmissions.map((x) => new Date(x.Timestamp)))
    await store.put(storeKey, maxTs)
    console.info("Cached new latest timestamp:", maxTs)
  }
}

Notice we’re loading the Slack webhook URL from our function’s environment variables. Let’s set up that webhook now.

Setting Up a Slack Webhook

Check out our guided example of how to build a Slack Webhook function here. Once you’ve generated your webhook URL, paste it into your function’s env vars (where we put the Google Sheet ID).

Schedule The Function To Run Every Minute

Click the “Schedule” button in the Napkin editor. Enable scheduling, select “Every Minute”, and then “Done”. The function will now run automatically every minute.

Test It!

Let’s test everything end-to-end. Submit a response to your form. Within a minute, you should get a Slack notification like this.

And that’s it! Now all that needs to be done is to go get some people to fill out your form 🙂

If you’re interested in getting Discord notifications, you can check out this example (Python version here) and integrate the code into the function we just created. If you have any questions, feel free to reach out for help on our Discord!

How to Get Google Form Responses on Slack