Script:
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('Index');
}
function processData(data) {
const lock = LockService.getScriptLock();
lock.tryLock(10000);
try {
const doc = SpreadsheetApp.openById(ScriptProperties.getProperty('key'));
const sheet = doc.getSheetByName(data['sheet']);
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const nextRow = sheet.getLastRow() 1;
const newRow = headers.map(function(header) {
return header === 'timestamp' ? new Date() : data[header];
});
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);
} catch(e) {
} finally {
lock.releaseLock();
}
}
Index.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form name="submit-to-google-sheet">
<label for="sheet">Sheet:</label><br>
<select id="sheet" name="sheet">
<option value="Local">Local</option>
<option value="Global">Global</option>
<option value="Dungeon">Dungeon</option>
</select>
<input name="email" type="email" placeholder="Email" required>
<input name="firstName" type="text" placeholder="First Name">
<input name="lastName" type="text" placeholder="Last Name">
<button type="submit" onclick="google.script.run.processData(this.form);">Send</button>
</form>
</body>
</html>
Input:
Output:
CodePudding user response:
Octavia, thankyou for the detailed proof. However, the answer to my question was this:
const sheet = doc.getSheetByName(e.parameter[sheetName])
I am able to refer to the incoming form data using the syntax e.parameter[<column header>]