You are here:
  • KB Home
  • Form data collecting to google spreadsheet
< Back

This article will walk you through how you can set up an easy to use form data collector script for your google spreadsheet, to be able to submit user-inputted data from your content.

 

First thing first you will need to create and set up your spreadsheet.

 

Now that your spreadsheet is created and you set up your different columns, we can add the insertion script.
From the top toolbar select the tools option and open the script editor.

You will see the following interface:

You need to replace all the predefined code with the following:

function doGet(event) {
const params = event.parameter;
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName(params.sheet);

sheet.appendRow(params.values.split(‘,’));
return HtmlService.createHtmlOutput(‘Success’);
}

After you inserted the script and named your project you can publish it as a webapp which we will be able to call later from a content.

Select deploy and choose the new deployment option.

Choose the Web app type for your deployment.

Configure your Web app like in the example below.

You can name your app anything that suits your project but you have to choose the executes as option to be Me and the Who has access to be Anyone.
These options will ensure we can submit data from the content without having to sign into a google account.

Click Deploy and Authorize access for the script.

When you presented with the following window click on Show Advanced and select Go to XY project (unsafe).
Google gives you this alert because the app we created in the previous steps has not been verified by them, and they think it can be a threat to your account.

Now sign in to your account and allow access to the app.

When your app is successfully deployed, you can copy the URL of it, we will use this in our next steps.

Now you can test the script.
Open the URL you just copied in the previous step in your browser, but extend it with the following query parameters:
?sheet=Sheet1&values=1,2,3
You can change this to fit your setup.

sheet=Sheet1  (The name of the sheet you want to input the values to).
values=1,2,3   (The values you want to input into the different columns separated by a comma, you can skip columns like this: 1,,3)

We are done with the setup of the spreadsheet, now we need to create a content that can use the webapp we made.

You can use any kind of interactive elements to collect data in your content, for this example, we will use a mix of input, radio-button, and checkbox elements.
To make later steps easier after you added your interactive elements name them according to what data they represent.

Configuring the different types of elements,

Input:
You will only need to fill out the Event name property of the element.

Radio buttons:
You need to use Radio buttons grouped, all the different elements will represent the same variable but with their own different values.

                       

Checkboxes:
For checkboxes, you can set the checked and unchecked values.

 

When you have all your interactive elements set up, the next step is to create a button that will collect the values and submit them to our spreadsheet Web app.
You can use almost any element as your button, it can be a text element or an image anything that has the touch events functionality.
Once you have your button set up, we need to create some touch events. Open the editor with the edit touch events button in the widget properties on the other tab.

Once you are in the popup click the Add new button to add a new action.

Set the action type to be Call API instead of the default Jump to.
The API URL should be the Web apps URL.

Now we need to collect the values of the interactive elements.
You can do this by adding sent fields to your action.

When adding these values make sure you add the Widget’s value type.
The left side of the value is where you can select which element’s value from which page you want to use, and the right side sets what’s it’s going to be called when referencing the value.

When all your parameters are added you will need to append them into the URL as query parameters.
The setup is the same as we were testing the webapp, but instead of setting static values you can reference the previously configured sent field values.

You can reference sent field values with their names between curly brackets like: {sentFieldValueOne} .
So your query param should look like: values={sentFieldValueOne},{sentFieldValueTwo},{sentFieldValueThree}
Make sure you don’t use any white spaces in your URL.
Your URL should look like this in the end:
https://script.google.com/macros/s/AKfycbwXXi7ZV-F58u3TTg/exec?sheet=Sheet1& values={sentFieldValueOne},{sentFieldValueTwo},{sentFieldValueThree}

Lastly set the Special property of the action to be GET, since this is the HTTP method we need to use.

Your overall setup should look like this:

 

The data submitting part of the content won’t work when previewing the content because of security reasons.
You can find a workaround for this here.

 

Extra custom parameters you can add to your submitted data

Timestamp:

Add a new sent field value and choose the Call function type.

 

 

 

 

 

 

Replace the default script with one of the following to get different time formats as your timestamp value:

UTC time string:
(receivedValue) => {
return new Date().toUTCString();
}

UNIX timestamp:
(receivedValue) => {
return Date.now();
}