อยากสอบถามหน่อยครับ
พอดีอยากลองศึกษาเกี่ยวกับนำ Google Sheet มาทำ Form Search ครับ เลยลองค้นหาใน Google แล้วเจอบทความ
https://www.bpwebs.com/create-web-forms-to-get-data-from-google-sheets/#code-file ดังตัวอย่าง
เลยลองทำตาม
โดยการ Copy Google Sheet มาแล้ว Add app script ตามตัวอย่าง
Index.html
[code]<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="
https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<script src="
https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<script src="
https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js" integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" crossorigin="anonymous"></script>
<!--##JAVASCRIPT FUNCTIONS ---------------------------------------------------- -->
<script>
//PREVENT FORMS FROM SUBMITTING / PREVENT DEFAULT BEHAVIOUR
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms
.addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
window.addEventListener("load", preventFormSubmit, true);
//HANDLE FORM SUBMISSION
function handleFormSubmit(formObject) {
google.script.run.withSuccessHandler(createTable).processForm(formObject);
document.getElementById("search-form").reset();
}
//CREATE THE DATA TABLE
function createTable(dataArray) {
if(dataArray && dataArray !== undefined && dataArray.length != 0){
var result = "<table class='table table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
"<thead style='white-space: nowrap'>"+
"<tr>"+ //Change table headings to match witht he Google Sheet
"<th scope='col'>ORDERNUMBER</th>"+
"<th scope='col'>QUANTITYORDERED</th>"+
"<th scope='col'>PRICEEACH</th>"+
"<th scope='col'>ORDERLINENUMBER</th>"+
"<th scope='col'>SALES</th>"+
"<th scope='col'>ORDERDATE</th>"+
"<th scope='col'>STATUS</th>"+
"<th scope='col'>QTR_ID</th>"+
"<th scope='col'>MONTH_ID</th>"+
"<th scope='col'>YEAR_ID</th>"+
"<th scope='col'>PRODUCTLINE</th>"+
"<th scope='col'>MSRP</th>"+
"<th scope='col'>PRODUCTCODE</th>"+
"<th scope='col'>CUSTOMERNAME</th>"+
"<th scope='col'>PHONE</th>"+
"<th scope='col'>ADDRESSLINE1</th>"+
"<th scope='col'>ADDRESSLINE2</th>"+
"<th scope='col'>CITY</th>"+
"<th scope='col'>STATE</th>"+
"<th scope='col'>POSTALCODE</th>"+
"<th scope='col'>COUNTRY</th>"+
"<th scope='col'>TERRITORY</th>"+
"<th scope='col'>CONTACTLASTNAME</th>"+
"<th scope='col'>CONTACTFIRSTNAME</th>"+
"<th scope='col'>DEALSIZE</th>"+
"</tr>"+
"</thead>";
for(var i=0; i<dataArray.length; i++) {
result += "<tr>";
for(var j=0; j<dataArray
.length; j++){
result += "<td>"+dataArray
[j]+"</td>";
}
result += "</tr>";
}
result += "</table>";
var div = document.getElementById('search-results');
div.innerHTML = result;
}else{
var div = document.getElementById('search-results');
//div.empty()
div.innerHTML = "Data not found!";
}
}
</script>
<!--##JAVASCRIPT FUNCTIONS ~ END ---------------------------------------------------- -->
</head>
<body>
<div class="container">
<br>
<div class="row">
<div class="col">
<!-- ## SEARCH FORM ------------------------------------------------ -->
<form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
<div class="form-group mb-2">
<label for="searchtext">Search Text</label>
</div>
<div class="form-group mx-sm-3 mb-2">
<input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Search Text">
</div>
<button type="submit" class="btn btn-primary mb-2">Search</button>
</form>
<!-- ## SEARCH FORM ~ END ------------------------------------------- -->
</div>
</div>
<div class="row">
<div class="col">
<!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
<div id="search-results" class="table-responsive">
<!-- The Data Table is inserted here by JavaScript -->
</div>
<!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
</div>
</div>
</div>
</body>
</html>
[/code]
--------------------------------------------------------------------------------------------------------------------------
code.gs
[code]function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
/* PROCESS FORM */
function processForm(formObject){
var result = "";
if(formObject.searchtext){//Execute if form passes search text
result = search(formObject.searchtext);
}
return result;
}
//SEARCH FOR MATCHED CONTENTS
function search(searchtext){
var spreadsheetId = '142FbylWc7109R0RsXQ42NuN_TFs-D3RTtIYJOSBR1QM'; //** CHANGE !!!
var dataRage = 'Data!A2:Y'; //** CHANGE !!!
var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRage).values;
var ar = [];
data.forEach(function(f) {
if (~f.indexOf(searchtext)) {
ar.push(f);
}
});
return ar;
}
[/code]
และทำการเปลี่ยน spreadsheetId แต่ทำเวลาลองทดสอบใช้งาน ฟังค์ชั่นมันถึงไม่ทำงานครับ
คือถ้าไม่มีการกรอกข้อมูลเข้าไป จะมีการแจ้งว่า Data not found! ตรงนี้ถูกต้อง แต่พอใส่ค่าเข้าไปแล้วกลับไม่เกิดผลเหมือนไม่มีการไปดึงข้อมูล
เลยอยากสอบถามว่าเราจะแก้ไขตรงนี้ได้อย่างไรครับ
อยากสอบถามเกี่ยวกับ App Script กับ google sheets ครับ
พอดีอยากลองศึกษาเกี่ยวกับนำ Google Sheet มาทำ Form Search ครับ เลยลองค้นหาใน Google แล้วเจอบทความ
https://www.bpwebs.com/create-web-forms-to-get-data-from-google-sheets/#code-file ดังตัวอย่าง
เลยลองทำตาม
โดยการ Copy Google Sheet มาแล้ว Add app script ตามตัวอย่าง
Index.html
[code]<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js" integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" crossorigin="anonymous"></script>
<!--##JAVASCRIPT FUNCTIONS ---------------------------------------------------- -->
<script>
//PREVENT FORMS FROM SUBMITTING / PREVENT DEFAULT BEHAVIOUR
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms.addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
window.addEventListener("load", preventFormSubmit, true);
//HANDLE FORM SUBMISSION
function handleFormSubmit(formObject) {
google.script.run.withSuccessHandler(createTable).processForm(formObject);
document.getElementById("search-form").reset();
}
//CREATE THE DATA TABLE
function createTable(dataArray) {
if(dataArray && dataArray !== undefined && dataArray.length != 0){
var result = "<table class='table table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
"<thead style='white-space: nowrap'>"+
"<tr>"+ //Change table headings to match witht he Google Sheet
"<th scope='col'>ORDERNUMBER</th>"+
"<th scope='col'>QUANTITYORDERED</th>"+
"<th scope='col'>PRICEEACH</th>"+
"<th scope='col'>ORDERLINENUMBER</th>"+
"<th scope='col'>SALES</th>"+
"<th scope='col'>ORDERDATE</th>"+
"<th scope='col'>STATUS</th>"+
"<th scope='col'>QTR_ID</th>"+
"<th scope='col'>MONTH_ID</th>"+
"<th scope='col'>YEAR_ID</th>"+
"<th scope='col'>PRODUCTLINE</th>"+
"<th scope='col'>MSRP</th>"+
"<th scope='col'>PRODUCTCODE</th>"+
"<th scope='col'>CUSTOMERNAME</th>"+
"<th scope='col'>PHONE</th>"+
"<th scope='col'>ADDRESSLINE1</th>"+
"<th scope='col'>ADDRESSLINE2</th>"+
"<th scope='col'>CITY</th>"+
"<th scope='col'>STATE</th>"+
"<th scope='col'>POSTALCODE</th>"+
"<th scope='col'>COUNTRY</th>"+
"<th scope='col'>TERRITORY</th>"+
"<th scope='col'>CONTACTLASTNAME</th>"+
"<th scope='col'>CONTACTFIRSTNAME</th>"+
"<th scope='col'>DEALSIZE</th>"+
"</tr>"+
"</thead>";
for(var i=0; i<dataArray.length; i++) {
result += "<tr>";
for(var j=0; j<dataArray.length; j++){
result += "<td>"+dataArray[j]+"</td>";
}
result += "</tr>";
}
result += "</table>";
var div = document.getElementById('search-results');
div.innerHTML = result;
}else{
var div = document.getElementById('search-results');
//div.empty()
div.innerHTML = "Data not found!";
}
}
</script>
<!--##JAVASCRIPT FUNCTIONS ~ END ---------------------------------------------------- -->
</head>
<body>
<div class="container">
<br>
<div class="row">
<div class="col">
<!-- ## SEARCH FORM ------------------------------------------------ -->
<form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
<div class="form-group mb-2">
<label for="searchtext">Search Text</label>
</div>
<div class="form-group mx-sm-3 mb-2">
<input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Search Text">
</div>
<button type="submit" class="btn btn-primary mb-2">Search</button>
</form>
<!-- ## SEARCH FORM ~ END ------------------------------------------- -->
</div>
</div>
<div class="row">
<div class="col">
<!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
<div id="search-results" class="table-responsive">
<!-- The Data Table is inserted here by JavaScript -->
</div>
<!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
</div>
</div>
</div>
</body>
</html>
[/code]
--------------------------------------------------------------------------------------------------------------------------
code.gs
[code]function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
/* PROCESS FORM */
function processForm(formObject){
var result = "";
if(formObject.searchtext){//Execute if form passes search text
result = search(formObject.searchtext);
}
return result;
}
//SEARCH FOR MATCHED CONTENTS
function search(searchtext){
var spreadsheetId = '142FbylWc7109R0RsXQ42NuN_TFs-D3RTtIYJOSBR1QM'; //** CHANGE !!!
var dataRage = 'Data!A2:Y'; //** CHANGE !!!
var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRage).values;
var ar = [];
data.forEach(function(f) {
if (~f.indexOf(searchtext)) {
ar.push(f);
}
});
return ar;
}
[/code]
และทำการเปลี่ยน spreadsheetId แต่ทำเวลาลองทดสอบใช้งาน ฟังค์ชั่นมันถึงไม่ทำงานครับ
คือถ้าไม่มีการกรอกข้อมูลเข้าไป จะมีการแจ้งว่า Data not found! ตรงนี้ถูกต้อง แต่พอใส่ค่าเข้าไปแล้วกลับไม่เกิดผลเหมือนไม่มีการไปดึงข้อมูล
เลยอยากสอบถามว่าเราจะแก้ไขตรงนี้ได้อย่างไรครับ