-
Create a new excel sheet
-
Inside the sheet, in
Tools
menu, click onscript editor
, and copy and paste the script from below -
Select function
Setup
, run it. -
From
Publish
menu, click onDeploy as web app
- enter Project Version name and click 'Save New Version'
- set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)*
-
Copy that deploy url, Example:
https://script.google.com/macros/s/AKfycbzlsdMgcBasdum7c14OU01mQ99nxV7p7ge6QmEuvS5w/exec
-
Paste that to
form action
attribute in your html page, like this:<form action="https://script.google.com/macros/s/AKfycbzltiXsdsdaruasdMgcBasdum7c14OU01mQ99nxV7p7ge6QmEuvS5w/exec" method="post"
-
Make sure that name field in HTML inputs is same as excel sheet headings. Example:
<input name="Parent's Name" .../>
So your sheet's first row could look something like:
Timestamp | Parent's Name | ....
(Timestamp is useful to record the time of submission)
Note: Casing is important: The value for name attribute in the input field should match exactly to the heading in the google sheet ```
8. To redirect to a different page on form submit:
- Insert this iframe code anywhere inside `body`.
(*Here, `thankyou.html` is the location of destination page to be redirected to.*)
``` <iframe name="hidden_iframe" id="hidden_iframe" style="display: none;" onload="if(submitted) {window.location='thankyou.html';}"></iframe> ```
- Then make the corresponding changes (adding onsubmit and target attribute) to the form element in HTML.
``` <form action="https://script.google.com/macros/s/AKfycbzltiXsdsdaruasdMgcBasdum7c14OU01mQ99nxV7p7ge6QmEuvS5w/exec" method="post" target="hidden_iframe" onsubmit="submitted=true;"> ```
-----
-----
## Script for Google Sheet
// 1. Enter sheet name where data is to be written below var SHEET_NAME = "Sheet1";
// 2. Run > setup // // 3. Publish > Deploy as web app // - enter Project Version name and click 'Save New Version' // - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously) // // 4. Copy the 'Current web app URL' and post this in your form/script action // // 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties();
// If you don't want to expose either GET or POST methods you can comment out the appropriate function function doGet(e){ return handleResponse(e); }
function doPost(e){ return handleResponse(e); }
function handleResponse(e) {
var lock = LockService.getPublicLock(); lock.waitLock(30000);
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;
var row = [];
for (i in headers){
if (headers[i] == "Timestamp"){
row.push(new Date());
} else {
row.push(e.parameter[headers[i]]);
}
}
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { lock.releaseLock(); } }
function setup() { var doc = SpreadsheetApp.getActiveSpreadsheet(); SCRIPT_PROP.setProperty("key", doc.getId()); }