Submitting Forms to Google Sheets
Hello World!
Since this is my first article, I simply have to start it with this phrase.
During the work on the project, the task arose to link the feedback form on the page with Google Spreadsheets. The essence of the project is to track the conversion of the team. There is little information on this topic on the Russian-speaking Internet, so I offer my solution.
To implement the task, you will need a google apps script and a feedback form.
I’ll get straight to the point.
Step 1: Create a New Google Doc
Here we need to mark up the table for the task. In my case, these are the names for the columns leadCount ma_name ma_email referer formid send.
Step 2: Let’s start creating the script
In the action feed Extensions → Apps Script
Instead of the myFunction function, paste the code below.
function doPost(e){
const sheet = SpreadsheetApp.getActiveSheet();
сonst n = sheet.getLastRow() + 1;
const d = new Date();
sheet.getRange("A"+n).setValue(e.parameter.p1); // leadCount
sheet.getRange("B"+n).setValue(e.parameter.p2); // ma_name
sheet.getRange("C"+n).setValue(e.parameter.p3); // ma_email
sheet.getRange("D"+n).setValue(e.parameter.p4); // refferer
sheet.getRange("E"+n).setValue(e.parameter.p5); // formid
sheet.getRange("F"+n).setValue(d);
return ContentService.createTextOutput().append(JSON.stringify({result: 'comlite'}))
}
Let’s consider how the script works.
function doPost(e)
– so Google will understand that the function must be called with a post request to the script.
const sheet = SpreadsheetApp.getActiveSheet();
– we receive the active table.
сonst n=sheet.getLastRow()+1;
– look for the number of the next empty line.
const d = new Date();
– write down the current date as dd.mm.yyyy.
sheet.getRange("A"+n).setValue(e.parameter.p1);
– we take the value of the first parameter from the received request and write it to the cell.
return ContentService.createTextOutput().append(JSON.stringify({result: 'comlite'}))
– return a response about the successful completion of the recording.
Step 3: Deploying the Script
Drag the mouse to the button Start Deployment → New Deployment.
Configuration Type → Web Application
Run → On my behalf (your email@gamil.com)
Who has access → Everyone
Click on the button – Start deployment
Google will ask for permission for this application and report that it is not safe, we agree and trust the application.
Step 4: Using the Script
After granting access, a link to use the application will appear, we will use it to access the script and add data.
Link like https://script.google.com/macros/s/DEPLOYMENT_ID/exec?p1=1&p2=An&p3=rrree@mail.ru&p4=site.ru&p5=form1
where p1, p2, p3, p4, p5 are the parameters from the script, which it will kindly put into the table.
Next, you need to send a POST request to this address from a form or from JavaScript. Thus, the form data will be sent to the Google spreadsheet and will be available for viewing.
In fact, the script will be able to work with a GET request, for this the function name should be doGet()
but doGet will be required to request data and display it on the page.