2. Connect a Google Form to Awesome Table

*** This page is deprecated. Please click here for the new version ***

Let users update your data / add the ability to read & write data

You can easily use Awesome Table with a Google Form. Create your form , post some responses, then add a new row in the spreadsheet between the headers and the first response (to indicate which filters you want to use). Example

And you can even use Awesome Table to create an Advanced Summary for your form. Example

Google Forms are very useful to let people submit new data in a spreadsheet but you can also use a form to let people update previous entries, clicking on the "Edit entry" button:

All you need for that is to store the unique URL letting you edit each form's response. And you can grab all those unique URLs with a little apps script.

First you need to open the spreadsheet and create a form, or, if you use the template sample Awesome Table Google Form , the form is already linked in the spreadsheet but as the responses in the database of the form are empty you need to re-populate with your own responses.

First case make your own Google Form :

Second case Edit the existing Google Form if you use the example :

Note: Beware, if you use the spreadsheet of the example we recommend you to not modify the structure of the linked form.

Then add a column containing the edit links what will be generated with the script.

In the spreadsheet click on the menu: Tools -> Script Editor

A new tab opens. You can see a first function which was written into the file, delete it and copy/paste the following script:

var formURL = 'https://docs.google.com/forms/d/18AuVZ2WTdox-x53DVM31mBzNL6QQ6SpO5JGJO0Et1cg/viewform';

var sheetName = 'Form Responses';

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] != '' && data[i][columnIndex - 1] == '') {

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);

}

}

}

Replace the first 3 rows of the script with your own parameters (the yellow parts) :

    • FormURL : copy/past the URL of your Form

    • sheetName : the name of the sheet which contains the form responses

    • columnIndex : the index of the column where you want to put the links to edit the form responses (e.g.: column H >> columnIndex = 8). It will not work if the column doesn't have a header ("Update" in our example).

Trigger the script through the menu "Run". A dialog will ask you permission to run the script. After that you should see the link to edit each form response un your spreadsheet.

But you certainly don't want to manually trigger the script every time you add a new entry. In the script editor, click on the menu Resources -> Current project's triggers and set up an automated trigger like that :

Save and... Done ! Now users will be able to edit each entry in your spreadsheet