To use activities or questionnaires, I believe that most of you will use google form to create table-filling functions very quickly. However, google forms have many limitations. I can’t create special fields, and I can’t make a complicated order form. Forms are too ugly (well… I’m not very familiar with google form settings).

Or don’t want users to click the URL to leave. In the end, the website can only insert the google form of the iframe and can’t trace the operation of filling in the table. The google analytics conversion target can’t be set either. These problems can be solved with the google excel api.


炸雞demo

fried chicken demo page

google excel spreadsheet performance

Here we must use Google spreadsheet to increase data collection to implement a webpage, simple salty chicken ordering service, the boss can also modify the processing status, so that customers can check before eating, in the end the boss did not help me make salt crisp Ji Le, still released me XD.

Just match html + css + javascript.
(Because there is a salty chicken near my house, I have to wait too long for each purchase, so I just want to play.)

google sheet method introduction

google offical document spreadsheet method.There is a very detailed introduction, including the ability to read fields, read data, sort data, insert data, etc. In fact, in some respects is a fully functional class database. If you are interested, you can click to see the files inside.

A lot of method inside the document, or direct implementation of relatively fast.

STEP.1 Open Google Drive Create google excel, create google AppScript

google creatExcel

google creatAppScript

STEP.2 Select google excel in AppScript

https://docs.google.com1JbXssixJaqTL_BMgnmOiMxLyAZm0jOkw173VdewqO9c1/edit#gid=0

STEP.3 Open AppScript plus just copied code and javascript

First, add parameters to myFunction, because you need to use the call api to bring in the data. Here are the name, phone, time, order, and price of the object you are expected to bring. These can be changed according to the name of the data you want to bring, and you can also delete it.

Followed by the google sheet method to select the form, followed by the code you just excel, and then use. GetSheets () [0] to select the first work form. This successfully selected the entire table.

Here’s the name of the first value received before processing, then select the google excel code and excel tab you want to fill.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
function doGet(e) {
//Take-in value to variable - Can be modified as required
var params = e.parameter;
var name = params.name;
var phone = params.phone;
var time = params.time;
var order = params.order;
var price = params.price;

//This is to choose this google excel
var SpreadSheet = SpreadsheetApp.openById("貼上你剛剛複製的編碼");
//get first form
var Sheet = SpreadSheet.getSheets()[0];

...
}

What is more rigorous here is to process the order number so that we can easily compare the data in the future, leaving the most important value that has just been brought in. Complete the simple insert data function!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
  ...
// Get LastRow = position of last line with data
var LastRow = Sheet.getLastRow();


// This is the processing order number - I first select the sixth column with the last line of data
var range = Sheet.getRange(LastRow, 6);
// Create order number (sixth column of the last row +1)
var orderNum = parseFloat(range.getValues())+1;
// If the last line is line 1, we need to change to order number 1.
if(LastRow == 1){
orderNum = 1;
}

//Start writing data is the same getRange select (line, column) setValue bring value name phone time order price values come in
Sheet.getRange(LastRow+1, 1).setValue(name);
Sheet.getRange(LastRow+1, 2).setValue(phone);
Sheet.getRange(LastRow+1, 3).setValue(time);
Sheet.getRange(LastRow+1, 4).setValue(order);
Sheet.getRange(LastRow+1, 5).setValue(price);
Sheet.getRange(LastRow+1, 6).setValue(orderNum);

//寫入結束後傳回true
return ContentService.createTextOutput(true);
}

STEP.4 Create debug.gs to test the just created data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//call
function debug() {
var Result = doGet(
{
"parameter": {
"name": "test",
"phone": "0912345678",
"time": "2018/02/10 22:46:00",
'order':"fried chicken * 1",
'price':"40",
}
}
);
Logger.log("Result: %s" , Result);
}

STEP.5 Edit Permissions

Allowing you to click the way to allow

This is a point to advance to xxx insecure
google userright
debugRun

STEP.6 Deploy as a web application

google deployAppScript
adjustAppscript
AppScriptapi

Copy the above URL, this is the URL of our API.

STEP.7 Create html concatenation data

Quickly and simply create a web page, click on the send below, then call send function asynchronous send data, and then successfully pass back to the user thanks.

The javasctipt on this page is dealing with no data to be blocked, as well as the number of items ordered.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

The first half is triggered call function
...
var data = {
'name' : name,
'phone':phone,
'time': filltime,
'order': order,
'price': price,
}
send(data)
}
function send(data){
$.ajax({
type: "get",
url: "https://script.google.com/macros/s/AKfycbz3fU3FVDmIQehtWX6ecKuV_A67lE64FEQ-ekmOZ_jZDyY96z0/exec",
data: data,
dataType: "JSON",
success: function (response) {
alert('thanks your order');
}
});
}

fried chicken demo page

fried chicken Excel

The owner reads and manages the order and the user inquires about the order and then adds it.

Summarize the steps:
Create excel, appscript -> edit appscript -> deploy public chaining -> web page front end api ->>> over

Reviews

Currently serving companies also often use google excel api, assuming that the information required to fill out the form needs to be shared for viewing, editing, and do not want partners to watch the company’s back office. Usually the google excel is used to process the forms. The advantage is that the establishment is very fast and does not need to be completed. Side help, as long as the front-end processing css + html + javascript about 2 hours to get.