How to submit a simple HTML form to a Google Sheet using only HTML and JavaScript.
This example shows how to set up a mailing list form that sends data to Google Sheets.
1. Set up a Google Sheet
Go to Google Sheets and create a new sheet. This is where we'll store the form data.
* Set the following headers in the first row:
A B C D E
Timestamp name email phone message
* Rename the form "Form Google Sheets"
2. Create a Google App Script
Click on Extensions -> Apps Script. This will open new Google Script. Rename it to something like "Form Script".
Now Replace the myFunction() {
section code with the following code snippet:
var SHEET_NAME = "Sheet1";
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
And Save the project before moving on to the next step.
3. Run the initialSetup function
You should see a modal asking for permissions. Click Review permissions and continue to the next screen. Because this script has not been reviewed by Google, it will generate a warning before you can continue. You must click the "Go to Mailing List (Unsafe)" for the script to have the correct permissions to update your form.
After giving the script the correct permissions, you should see the following output in the script editor console:
Now your script has the correct permissions to continue to the next step.
4. Add a trigger for the script
Select the project "Triggers" from the sidebar and then click the Add Trigger button.
In the window that appears, select the following options:
Choose which function to run: doPost
Choose which deployment should run: Head
Select event source: From spreadsheet
Select event type: On form submit
Then select "Save".
5. Publish the project
Now your project is ready to publish. Select the Deploy button and New Deployment from the drop-down.
Click the "Select type" icon and select Web app.
In the form that appears, select the following options:
Description: Mailing List Form (This can be anything that you want. Just make it descriptive.)
Web app → Execute As: Me
Web app → Who has access: Anyone
Then click Deploy.
Important: Copy and save the web app URL before moving on to the next step.
6. Configure your HTML form
Create a HTML form named "index.html" and paste the following code.
And replacing YOUR_WEBAPP_URL with the URL you saved from the previous step then save the file.
Now when you submit this form from any location, the data will be saved in the Google Sheet.