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.