[筆記] 從google表單新增的資料，如何在google sheet 自動再計算

10 min readOct 27, 2018

前言

故事是這樣的，小弟我爸媽有經營一個小成衣廠，以前帳目都是我媽每個月手算的，但因為手寫的賬目很難整理，每次都要花費 1–2天計算不說，還很容易錯誤。

剛好昨天我媽也在問平板有沒有辦法可以很簡單的輸入資料後，幫他都算好這樣。所以我決定幫他做一個可以用手機key in的填單系統，然後可以看到報表。

主要需求有 2個：

可以輸入每個車縫阿姨，每一批車的件數、單價與工作類別 ( ex : 平車 /拷克 ) ，進而算出每個阿姨每個月要領多少薪資。
可以輸入每批不同商品製作的數量與出貨的數量，進而算出庫存數量與出貨金額。 (當然還有計算每月出貨量，店家佔的營收比例，不同商品銷量比例等等… )

需求看起來也不複雜，所以我預計用google form來登記資料，然後透過google sheet 做幾個樞紐表，來實現這件事。

我準備了三個google form :

車縫登記 : 可以登記商品編號、車縫件數、車縫單價、車縫人員等..，預計透過 “車縫數量*單價“來算出每個阿姨的工資。
商品登記：可以登記商品編號、定價，主要是每次有新商品就登記進來，之後用來算出貨金額的。未來等我媽熟悉流程之後，應該會讓他把成本也在第一次就登記進去，包含像是布錢，各個部位的車縫價錢等等..。這樣之後就能算淨利，其他表的登記欄位就會變得比較少了。
庫存/出貨登記 : 可以登記商品編號、裁剪數量、出貨數量、出貨店家等..，預計透過“裁剪數量-出貨數量” 算出庫存數量。”出貨數量 * 商品單價” 算出每一單的出貨金額。

遇到問題

結果事情果然沒想像的那麼單純，當時我想的是這樣：

如上圖，我想像的就是，當每一筆資料新增進來的時候，總和那邊就會自動把件數跟單價相乘，算出我要的結果。

但實際上是這樣：

總之看起來就像是，從google form進來的資料，其實像是新增一個row，所以公式也會消失，當然包含跨sheet的公式也是不行。

解法

來到正題，上網查了一下，別人是說有一種add-on叫做copydown好像能解，但它要的權限實在是太多了，我沒種授權，只好靠自己了。

最後是透過google script解決這件事的，邏輯是 : “新增資料時會新增row，所以我們要在新增資料之後，才去進行計算，並填寫到正確欄位”

編碼器的位置 :

首先先設定一下，每次觸發這個腳本運行的event：

紅匡處設定的一模一樣就好了，主要就是在說 : “當每次google form提交表單的時候，就執行一次”

接下來google script 會跟你要 google sheet 的授權，就給他授權就好，然後就開始來寫code囉 (抖)

先貼上我寫的腳本，再一一解釋 :

var 這個東西就是用來宣告變數，當你今天在腳本裡面創造了一個新的名字，例如SpreadSheet。當他第一次出現的時候，他前面就需要加var，不然就會掛掉。

總之這邊有個邏輯就是，基本上都需要去假設變數，有一點像以前的：x = 5 + 8 and y = x + 6 之類的。

var SpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var name = ‘車縫登記’;
var sheet = SpreadSheet.getSheetByName(name);

SpreadsheetApp.getActiveSpreadsheet() 代表獲取目前這個google sheet的位置之類的吧，總之當有了這個之後。(這邊我定義 google sheet的位置叫做 ”SpreadSheet”)

可以透過 .getSheetByName(name) 獲取目前這個google sheet中，sheet名稱為 “name” 的 table 位置。像我這邊sheet(table)名字就叫做”車縫登記”，接下來就能在“車縫登記”這個sheet做讀取跟寫入了。(這邊我定義 sheet 的位置叫做 “sheet“)

var Avals = sheet.getRange(“A1:A”).getValues();
var Alast = Avals.filter(String).length;

這兩行，簡單翻譯就是，獲取A column目前有值的欄位數量。目的是為了得到A欄最後一列 (row) 的位置，因為從google form過來的資料，A欄的時間戳記，都是自動必填的，舉例：像下圖的Alast(最後一列)就會是 6

得到最後一列位置的目的是，之後每次新增一筆資料時，我們就會知道那列的位置，進而把該列的資料讀取計算後再寫入正確位置。

Logger.log(Alast) 主要是用印出資料用的，我通常用來測試變數值有沒有正確，每次寫一段落存擋之後，可以點選上方的運行(三角形 icon)。然後你可以在上方的 “查看” > “紀錄”。就可以查看全部有被你印出來的值。當然整個寫好後確認沒有問題，就可以把這段拔掉了。

var range = sheet.getRange(Alast,4,1,1);

sheet.getRange(Alast,4,1,1)，getRange()用途是取得某個範圍，裡面需要放4個數字，分別的意義是 1 : row 位置 , 2 : column位置 , 3&4 : 主要指的是縱橫取多少格，但總之我們只要取單格數字，所以通常都用 1,1 就好。

再看看這邊 sheet.getRange(Alast,4,1,1)，有發現我們用到了Alast嗎？就是上面算出來最後一列的“列數”，而這個式子的意思就是 = 取最後一列(row)的第四欄(col)，就是下圖的位置。

var count = range.getValues();
for (var row in count) {
for (var col in count[row]) {
count = count[row][col];
} };

range.getValues() 指的是，獲取這個範圍的值，在這個case就是件數，所以我把它命名為 “count”。

而下邊那堆東西，主要使用的原因是因為，從google sheet裡面拿出來的資料，他的格式其實是長這樣 [[100]]，google script 叫他做object物件，但這個object物件是不能做計算的 (我測試其實可以相乘，但我想說大家可能希望加減乘除..)

所以下面這堆東西呢，目的就是為了把 [[100]] 變成 100，以便我們可以進行計算。

var range = sheet.getRange(Alast,5,1,1);
var price = range.getValues();
for (var row in price) {
for (var col in price[row]) {
price = price[row][col];
}
};

同理，我獲取了下圖這個欄位的值：

因為他是第5欄(col)，所以getRange是(Alast,5,1,1)。因為是單價，所以我命名叫做price

var totalvalue = [[price*count]];
var range = sheet.getRange(Alast,7,1,1);
range.setValues(totalvalue);

接下來就是要計算出總和，並且把總和寫入最後一列，第七欄的位置。為何會是最後一列 ? 還記得一開始我們有設定嗎，每次google表單被送出時，這個腳本就會運行一次。

google表單被送出 > 增加一筆新的資料在最後一列 > 運行腳本計算出新資料的總和並寫入最後一列第七欄。

var totalvalue = [[price*count]] ，我設定總和叫做”totalvalue”，他等於price*count。而至於為何要列成[[price*count]]，主要是因為格式問題，剛剛有說過從google sheet讀取出來的都是object物件，所以只有object物件，才能寫入google sheet中。

以剛剛上面那個case為例 price*count = 550，我們要把它變成 [[550]]，才能寫入google sheet中，所以我們只要列[[price*count]]就可以了。(這個真的比我想像的隨便xd)

var range = sheet.getRange(Alast,7,1,1); 一樣，首先獲取最後一列，總和欄位的位置