Allow users to update your app’s data with Google Forms

In this article, you will learn how to use Google Forms to allow users to update (edit) your Awesome Table app’s content. Examples using this setup include, but are not limited to:

A school project where your students update your class’ digital library.
A digital kiosk for your staff where they can update your movie database.
A job posting board for your recruitment team.

info This article is only for Google users.

not_interested This method will allow anyone to edit the responses in your data source. We highly recommend that the app should only be shared with the proper/targeted audience.

warning This is an advanced topic. It is a bit involved, but easy to follow and implement.

How does it work?

For this setup to work:

a) Share the relevant app to your targeted audience.

b) Instruct your users to click the edit button to modify the content of a particular record.

c) A new tab will launch the relevant row (record) in Google Forms allowing your users to revise the content as needed.

d) Once the update is submitted, your Awesome Table app will reflect the changes made.

Live example

Copy template

info You can also use the method discussed here to create Charts for your form entries.

Prerequisites

The following must be present for the entire procedure to work:

Gmail or Google Workspace account
Google Forms (with responses)
Google Sheets containing form responses (that’s actively linked to Google Forms)

Setting up the workflow

The entire procedure involves several parts (Google Apps) with interdependencies. It is complex but easy to follow and implement. The entire procedure can be a bit involved. But once everything’s configured, existing rows of data and newly submitted form entries will automatically have form-edit link buttons that are accessible from your app.

1. Google Forms (with responses)

First, we need to activate a setting in Google Forms that is crucial in the workflow’s functionality.

a) Open the relevant Google Forms then click the gear icon.

b) Tick the box for Edit after submit.

c) Click Save.

warning This is a vital step in this procedure. If this is skipped, the entire workflow will not function.

2. Google Sheets

Next, we’ll prepare our Google Sheets data source to house the edit-links that we’ll use later on to open the form.

Set up the script

On your Form Response sheet (that’s actively linked to your Google Forms), add a blank column and label it appropriately (Link in our example). This will later contain the edit links that will be generated by the script.

Then click Extensions > Apps Script.

A new tab will open containing a blank Google Apps Script project. Delete everything in the editor then copy and paste the entire script below:

var formURL = 'https://docs.google.com/forms/d/yourId/viewform';
var sheetName = 'Form Responses 1';
var columnIndex = 8;

function getEditResponseUrls() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = 2; i < data.length; i++) {
    if (data[i][0] != '') {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if (formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex).setValue(editResponseUrl);
    }
  }
}

The code may be daunting. But we only need to focus on 3 important sections (highlighted in red): formURL, sheetName, and columnIndex.

Properties

Instructions

formURL

Replace yourId with that of your own form ID.
To do this in Google Sheets, click Form > Edit form.

Then copy the long string of numbers (until the edit section) and paste this to replace the yourId placeholder.

The form ID from the form-edit’s URL

In our example, the form’s complete URL is (form ID highlighted in red):

https://docs.google.com/forms/d/1oF6sfByttYYfMMMdWcp5UyOlFrz79WN_cTRaTIXbGDg/viewform

sheetName

Update the value of sheetName with the name of your response sheet. Form Responses 1 in our example.

columnIndex

Replace 8 with the correct column index of your edit-link column. In our example, column H (Link) is on the 8th column index. (That is: A is 1, Z is 26, AA is 27).

warning Make sure that the edit-link column has a header (label). Otherwise, the script will not work.

Here’s how our script will look like once completed.

Next, save the project (we used the project name JobBoardGetFormLink in this example). Then click OK.

Run the script

After saving the script (function), click the Run icon. Next, select Review Permissions. Then choose a Gmail / Google Workspace account to be used in this project.

A warning message will be displayed by Google when you run the function for the first time saying that This app isn’t verified. But since we created the script ourselves, you can be assured that this is risk-free.

Click Advanced then Go to <your script name> (unsafe) link to run the script.

Then click Allow.

info More on why Awesome Table needs those permissions.

After which, the script will start generating the appropriate links for each row of data.

Automatically generated links in the data source (via our function).

Configure the Edit button in Google Sheets

Now that we have the form-links, we’re going to create an edit-button using our buttonType keyword.

Create a new column for the edit links (the header’s labeled as Update in this example). We’ll be using the buttonType keyword to enhance the presentation of the edit links. Read more on how to use buttonType.

info You can learn more about using the ButtonType keyword.