Google Sheets 略過空白格重整資料

Google Sheets 略過空白格重整資料

本篇要解決的問題

前幾天有朋友問說,能不能在 Excel 上,跳過空白儲存格,重整一份資料?像這樣:

略過空白格重整資料
略過空白格重整資料

找出會出席婚禮的朋友,並整理成新的名單排列在新欄位上。

Excel 上處理的方式就是用公式,Google 了幾個找到的公式後……放棄了,因為看不懂 XD~

三、四個公式合在一起用,還都寫在一行中,覺得一陣暈眩。

一開始跟朋友說搞不定,但後來想想,身為一位前端工程師,Excel 公式不會,難道不能寫程式解決嗎!?

要寫程式就只能用 Google 試算表 + 指令碼編輯器,因此這篇主要是在 Google Apps Script 寫程式來處理。

不會寫程式的朋友,就複製貼上程式碼吧~


新增 Google 試算表、指令碼編輯器

進到 Google 雲端硬碟後,新增一個 Google 試算表的檔案,這邊我們填上測試資料,像這樣:

測試資料
測試資料

資料完成,接著是新增這份試算表的指令碼。

點擊「工具 > 指令碼編輯器」:

點擊 工具 > 指令碼編輯器
點擊 工具 > 指令碼編輯器

接著就會進到 Google Apps Script 的頁面:

放個廣告賺點養主機的$$,謝謝

Google Apps Script 的頁面
Google Apps Script 的頁面

我們刪掉預設有的 myFunction 然後修改檔案名稱進行存檔,這一步就完成了。


程式碼:onEdit

參考文件:Google Sheets eventsClass Spreadsheet

看了參考文件,在編輯 Sheet 時,會自動觸發 onEdit 事件,因此我們只需要將 function 的名稱設為 function onEdit 就行,這樣當試算表有更動,就會自動執行 onEdit 中我們寫的程式。

在開始寫 code 之前,要先確認之後整理出來的新名單是從哪一個儲存格開始往下新增?

就範例來看,會從 D2 這格往下增:

以 D2 為起點往下新增
以 D2 為起點往下新增

確認好後就可以開始寫程式了。

確認一下程式要寫的功能步驟:

  1. 清空 D2 以下的儲存格
  2. 判斷 B 那列有沒有填「是」
  3. 有填「是」,就抓 A 那列的值依序放到 D2 以下

本篇的 demo 會多加一項,假設有多張表,我們只需要其中一張表要執行這功能,就要額外加上對「表名」的判斷。

寫在「程式碼.gs」的 code 如下:

一開始的:

var column = 'D'; // ***哪一欄
var row = 2; // ***哪一列開始往下改

就是在寫要從哪一個儲存格往下改,我們是從「D2」開始,因此 column 寫 D、row 寫 2。

var sheet = e.source;

文件上寫 source 是指試算表本身,為了取得表名就要先命出來。

var sheetName = sheet.getSheetName();

這行就是取表名,因為我們想在指定的表才執行功能,因此需要有表名來比對。

var data = sheet.getDataRange().getValues();

這行是取儲存格的值,我們把抓到的值丟到儲存格看一下會長什麼樣:

[
  ["朋友","出席婚禮?","","出席名單"],
  ["好人1","","",""],
  ["好人2","","",""],
  ["好人3","","",""],
  ["好人4","","",""],
  ["好人5","","",""]
]

比對我們的試算表,可以知道一列就會成為一陣列。

那在判斷每列的第二欄有沒有填「是」,就只要寫 data[i][1] == "是" 便能判斷了。

要抓第一欄的值也是寫 data[i][0] 便能抓到。

if(sheetName === '工作表1') {}

這句就是判斷是不是在「工作表1」的表中,如果每張表都要用的話可以刪掉。

var x = row;
for(var i = 0, len = data.length; i < len; i++) {
  sheet.getRange(column + x).setValue('');
  x++
}

這個 for 迴圈是為了先清空以前新增的,因為有可能第一次填表時有 5 人來,但後來又改成只有 2 人來,若不清空就塞值進去,第一次後面的 3 人就會還留在表上,所以要先清空。

x = row;
for(var i = 0, len = data.length; i < len; i++) {
  if(data[i][1] == "是") {
    sheet.getRange(column + x).setValue(data[i][0]);
    x++;
  }
}

這個迴圈就是在塞資料了,判斷第二欄有沒有填「是」,有的話就抓第一欄的值塞到 D[x] 的儲存格裡。


測試結果

最後我們來測一下結果:

略過空白格測試
略過空白格測試

可以看到是成功的。


彩蛋:可以指定不同的試算表

本段以上都是在目前的試算表上作用,其實也可以直接在指定的試算表上作用。

這邊會需要額外確認權限,必須要是指定的試算表擁有者才能確認的,可以防止被惡意改動。

程式碼如下:

第一個 id 可以直接在試算表的網址上看到,比如我們的網址是這樣:

https://docs.google.com/spreadsheets/d/aaaaaaabbbbbbbccccccc/edit#gid=0

那「aaaaaaabbbbbbbccccccc」就會是 id,複製後填到變數裡就可以。

程式碼寫完後,點擊「編輯 > 現有專案的啟動程序」,最後新增一個觸發條件後就完成了。

Summary
Google Sheets 略過空白格重整資料
Article Name
Google Sheets 略過空白格重整資料
Description
本篇大綱:本篇要解決的問題。新增 Google 試算表、指令碼編輯器。程式碼:onEdit。測試結果。彩蛋:可以指定不同的試算表。前幾天有朋友問說,能不能在 Excel 上,跳過空白儲存格,重整一份資料?身為一位前端工程師,Excel 公式不會,難道不能寫程式解決嗎!?
Augustus
Let's Write
Let's Write
https://letswrite.tw/wp-content/uploads/2020/08/logo_512.jpg
訂閱
通知
guest
0 Comments
Inline Feedbacks
看所有留言