Index.html
There are five input option in the html form
<form id="collegeInfo" onsubmit="handleFormSubmit()">
<div class="mb-1">
<div class="form-group col">
<label for="districtName" class="form-label">Please Select District of the Institution</label>
<select class="form-select form-select-sm" id="districtName" name="districtName" required>
<option>--Select District--</option>
</select>
</div>
</div>
<div class="mb-1">
<div class="form-group col">
<label for="upazilaName" class="form-label">Please Select Upazila of the Institution</label>
<select class="form-select form-select-sm" id="upazilaName" name="upazilaName" required>
<option>--Select Upazila--</option>
</select>
</div>
</div>
<div class="mb-1">
<label for="name" class="form-label">Enter the full name of the Institution:</label>
<input type="text" id="name" name="name" class="form-control form-control-sm" required>
</div>
<div class="mb-1">
<label for="email" class="form-label">Enter E-mail of the Institution:</label>
<input type="text" id="email" name="email" class="form-control form-control-sm" required>
</div>
<div class="mb-1">
<label for="eiin" class="form-label">Enter EIIN of the Institution:</label>
<input type="number" id="eiin" name="eiin" class="form-control form-control-sm" placeholder="Enter English Digits only" required>
</div>
<button id="collegeInfo-submit-btn" type="submit" class="btn btn-primary">Submit</button>
</form>
JavaScript
function handleFormSubmit() {
var formData = readFormData();
google.script.run.withSuccessHandler(disAlertMsg).processFormCreate(formData);
function disAlertMsg(){
Swal.fire({
position: 'center',
icon: 'success',
title: 'Submitted Successfully',
showConfirmButton: true,
timer: 1500
})
}
document.getElementById("collegeInfo").reset();
}
function readFormData() {
var formData = {};
formData["districtName"] = document.getElementById("districtName").value;
formData["upazilaName"] = document.getElementById("upazilaName").value;
formData["name"] = document.getElementById("name").value;
formData["email"] = document.getElementById("email").value;
formData["eiin"] = document.getElementById("eiin").value;
return formData;
}
Code.gs
I am trying to append html form value into google sheet starting from second Column (B Column) using Sheet api, but can’t success
const SPREADSHEETID = "11uGKfy0Jh-24-RI-iIdjGhDtWxwhgiKRHT8JS9guP-Q";
const DATARANGE = "Form responses 1!B2:F";
const DATASHEET = "Form responses 1";
function doGet(request) {
let html = HtmlService.createTemplateFromFile('Index').evaluate();
let htmlOutput = HtmlService.createHtmlOutput(html);
htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');
return htmlOutput;
}
function getIndex() {
return HtmlService.createHtmlOutputFromFile("Index").getContent();
}
//INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
function processFormCreate(formObject) {
var sss = SpreadsheetApp.openById('11uGKfy0Jh-24-RI-iIdjGhDtWxwhgiKRHT8JS9guP-Q');
var ss = sss.getSheetByName('Form responses 1');
let values = [[
formObject.districtName,
formObject.upazilaName,
formObject.name,
formObject.email,
formObject.eiin
]];
createRecord(values);
}
function createRecord(values) {
try {
let valueRange = Sheets.newRowData();
valueRange.values = values;
let appendRequest = Sheets.newAppendCellsRequest();
appendRequest.sheetId = SPREADSHEETID;
appendRequest.rows = valueRange;
Sheets.Spreadsheets.Values.append(valueRange, SPREADSHEETID, DATARANGE, { valueInputOption: "RAW" });
} catch (err) {
console.log('Failed with error %s', err.message);
}
}
I tried with above code, but every time form value is appended to google sheet starting from first Column (A Column), But I want to append form value to google sheet starting from second Column (B Column), Please help me………..