Google Suite I. Create a Google Sheet from an Form Submission event.

Feb 4, 2024

This is the first in a series of blog posts that show how to automate simple tasks using Google’s suite.

In this chapter, we’re going to see how to create a Sheet in an event-based fashion. Keep reading to get some context on a potential use case this automation would solve.

Once you implement this automation, you will have a Form that creates a Google Sheet every time a new response is submitted. The Google Sheet’s name will be based on the content of the form submitted.

💡 The purpose of this article is show case a simple automation structure with which to get started. More advanced use cases can be built using this structure. But are not part of this guide.

Requirements

For this guide we will need:

  • A Google Account

  • Basic Javascript knowledge

And we will use Google’s

  • Forms

  • Sheets

  • Apps Script

Why is this useful?

This can be useful in many circumstances. Let’s cover one use case here, and if you think of any other – please add it to the comments below!

As a very successful freelancer, you have lots of customers. Yay! Since you are a tenured <your profession here> , you bill them regularly for all the dollars it costs to have an expert as a provider. Since you already work with Google, your tool of choice to create these bills is Google, where you can easily duplicate Sheets to create new bills. The process however is tedious, and as you scale, you have too many customers to spend time in this task.

This might sound as a convoluted use case, but you can sure imagine other use cases such as making customers fill in details in a form to automatically generate a proposal in a Sheet. Maybe even export it as a PDF? (we can talk about that in a different chapter).

Create a form

We need to start by creating a form in Google Forms, so head there, and create a simple form with 1 single field as a Short-answer question.


  1. Create a Form in Google Forms

  2. Add a question. A simple Short-answer question will do, and will be useful to use this string for the Sheets’ name later on.

  3. Configure your form so it sends responses to a Sheet in Google Sheets

    1. Go to the Responses tab

    2. Click on Link to Sheets

    3. Ensure Accepting responses switch is enabled

  4. Test your form by submitting a response. Verify the response gets added to the Sheet that’s linked to the Form.

Create Script

We now need to create a script that will execute some code every time a response to our form is submitted. To do this, we will create a new Apps Script project, add some functions and triggers. And let the magic begin.

  1. Go to your form, and click on the 3 dot menu on the top right


  2. Click on Script editor. This will create a new project where we can start coding

Code

Information gathering

  1. Open the Google Sheet where the responses are being logged – we need two values for our script to reference this sheet:

    1. Column: Find, and take note of the column where the Name of the Customer is logged. In our example this is column C.

    2. Sheet ID: Find the sheet ID, which you can find in the URL just after the string https://docs.google.com/spreadsheets/d in this example it’s the string starting with LsDjW6…

    3. Folder ID: You can find the folder ID in drive in the URL after the string https://drive.google.com/drive/u/0/folders/ in this example it’s the string starting with xrDfsms…


Coding

  1. Open the Code.gs file and paste the code below.

  2. Change the values for those you took note of in the contstant variables defined at the start of the file (SHEET_ID and NAME_COLUMN)

/**
 * This function will trigger everytime
 */
const SHEET_ID = "LsDjW6UlRtxi.....yOktVTG9E"
const NAME_COLUMN = "C"
const FOLDER_ID = "xrDfsmsam......vwGA6MVxh"

function onResponseSubmitted(e, i){
  var sheet = SpreadsheetApp.openById(SHEET_ID)
  var valueInColumnC = sheet.getRange(NAME_COLUMN+parseInt(sheet.getLastRow())).getValue();
  Logger.log("Value in Column C of the new row: " + valueInColumnC);
  createNewSheet(valueInColumnC);
}

function createNewSheet(valueInColumnC) {
  // Create a new Google Sheet in your Drive
  var newSheet = SpreadsheetApp.create("New Sheet - " + valueInColumnC);
  // Move the new sheet to the specified folder
  var file = DriveApp.getFileById(newSheet.getId());
  var folder = DriveApp.getFolderById(FOLDER_ID);
  file.moveTo(folder);

  Logger.log("New sheet created with ID: " + newSheet.getId() + " in folder: " + folder.getName());
}

  1. Click on Save project. This will save progress.

  2. Click on Run. This will attempt to run the code to check it funs fine. At the same time it will verify it has permissions to access all the resources you attempt to modify (Sheets, Drive, Forms, etc), so you will get prompted to grant the script access those before it can work.

🙌 Tap yourself on the back, you can now try submitting your form, and verify it creates a sheet in the corresponding folder