How to Connect Typeform to Google Sheets and Automate Diversity Data Collection

Learn how to connect your Typeform survey to Google Sheets and automate the collection of diversity data with step-by-step instructions.

August 19, 2024 By Admin/Ops

In this guide, we'll learn how to connect your Typeform survey to a Google Sheet and automate the process of updating diversity data.

Let's get started

First, go to the connect page of your Typeform survey and ensure it is connected to a Google Sheet. If it is not, connect it.

Connecting Typeform to Google Sheets

Step #1: Click on "Connect" — Click on "Connect"

Step #2: Select your account and sign in — Select your account and sign in

Step #3: Once you sign in, Typeform will ask if you want to create a new sheet or use an existing one for your form responses. In most cases, you will create a new sheet. — Once you sign in, Typeform will ask if you want to create a new sheet or use an existing one for your form responses. In most cases, you will create a new sheet.

Step #4: Name this something memorable. I usually include "responses" to make it easy to find. — Name this something memorable. I usually include "responses" to make it easy to find.

Step #5: Click on "Activate integration" — Click on "Activate integration"

Add diversity data to Google sheet: Set up appscript

Step #6: Click on ". View your spreadsheet" — Click on ". View your spreadsheet"

Step #7: In the last column, write "Gender" as that is the first answer we will get from those who fill out the form. — In the last column, write "Gender" as that is the first answer we will get from those who fill out the form.

Step #8: Go to Extensions and select App Script. — Go to Extensions and select App Script.

Step #9: Next, input the code below and update all the values. Replace the entire <PLACEHOLDER>, including carats, with the information requested. — Next, input the code below and update all the values. Replace the entire <PLACEHOLDER>, including carats, with the information requested.

Formula to use:

function FillFormulas() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('<YOUR SHEET NAME>');

var lastRow = spreadsheet.getLastRow();

spreadsheet.getRange("<FIRST CELL OF DIVERSITY DATA COLUMN>").setFormula("=IF(<FIRST CELL OF OPT IN COLUMN>=\"I opt in to helping WoCl shape a more diverse climate workforce.\",INDEX(IMPORTRANGE(\"<YOUR TEAM DIVERSITY INFO URL>",\"DO NOT EDIT!A:N\"),MATCH(<FIRST TIMESTAMP CELL>,IMPORTRANGE(\"<YOUR TEAM DIVERSITY INFO URL>",\"DO NOT EDIT!M:M\"),-1)),\"Opted out\")");

var fillDownRange = spreadsheet.getRange(2,<NUMBER OF YOUR DIVERSITY DATA COLUMN>,(lastRow-1)); spreadsheet.getRange("<FIRST CELL OF DIVERSITY DATA COLUMN>").copyTo(fillDownRange);

}

Step #10: Rename your project and save. — Rename your project and save.

Step #11: Click the trigger button on the left-hand side. — Click the trigger button on the left-hand side.

Step #12: Click on "create a new trigger" — Click on "create a new trigger"

Step #13: Leave all the values as they are. Click on "Save" — Leave all the values as they are. Click on "Save"

Step #14: Once you sign in again to authorize your app, you should be all good! Check and makes sure it works though! — Once you sign in again to authorize your app, you should be all good! Check and makes sure it works though!