Google 試算表裡面「合併工作頁」及「特定欄位包含特定元素則刪除該行」的巨集

Macros of "merge work sheet" and "delete rows that cell of specific column contained specific elements " in Google online sheet.

之前有將一個google試算表中很多工作頁的工項合併在一起的需求，又不想一直複製貼上，所以上網google找到了一些方法

最方便其實是去股哥工作室市集 Google Workspace Marketplace下載別人已經寫好的擴充套件最簡單，比如這個。但是缺點是只能試用30天，之後就要花錢訂閱。免費主義的我是不會選擇這條路的XD

同時偶爾會需要特定欄位出現特定元素的時候要刪除那一行的狀況（比如第3欄為空白或是等於多少的時候就要刪除那一行等等）

以下提供巨集Code達成以上兩個功能。（不過很多google試算表一次合併的方法我還不知道......）

合併工作表：

Step0：建立一個google試算表，裡面有你想合併的工作頁，這邊我很簡單的建立了一個含三頁的測試用試算表。(名字隨意)

Step1：在想要合併工作表的最前面建立一個Totalwork分頁，名稱可以不一樣，但是一定要在第一頁。

Step2：進入工具中的指令編輯器

Step3：複製貼上下面的代碼後儲存(功能跟標註都已經寫好了，Code是東拼西湊從網站上取得的。)

如果Step1第一頁的匯總表不叫作Totalwork的話，請記得改這一行

spreadsheet.setActiveSheet(spreadsheet.getSheetByName(“Totalwork”), true);

裡面的工作表名稱，不然會報錯。

↓完整Code↓：

/** @OnlyCurrentDoc */
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetlen = ss.getSheets();
function TotalsSheet() {
 var totaldata = [];
 var sheets = ss.getSheets();
 // ↓1=要開始統計的工作表示第幾號位，因為範例中第一個工作表是Totalwork，所以從1開始(第一個工作表是0號位)
 // PS:這個方法totalsheet要放在第一個工作表
 starSheet = 1
 for (var i = starSheet ; i < sheetlen.length; i++) {
 var sheet = sheets[i];
 var range = sheet.getDataRange();
 var values = range.getValues();
 // ↓第一個複製的工作表連標題都一起複製
 if (i == starSheet) {
 for (var row in values) {
 totaldata.push(values[row]);
 }
 }
 // ↓後面的工作表第一行就不複製了
 else{
 for (var row in values) {
 if (row != 0) {
 totaldata.push(values[row]);
 }
 }
 }
 }
 return totaldata; 
}
function Combine() {
 var spreadsheet = SpreadsheetApp.getActive();
 // ↓Totalwork要改成自己要彙總所有工作的工作表的名稱，這個工作表要放在第一個
 spreadsheet.setActiveSheet(spreadsheet.getSheetByName(“Totalwork”), true);
 // ↓刪除現有內容
 var sheet = spreadsheet.getActiveSheet();
 var currentCell = spreadsheet.getCurrentCell();
 sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
 currentCell.activateAsCurrentCell();
 spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
 // ↑刪除現有內容
 // 合併儲存格
 var totaldata = TotalsSheet();
 for (var i = 0; i < totaldata.length; i++) {
 spreadsheet.appendRow(totaldata[i]);
 }
};
//function Start() {
// var All = SpreadsheetApp.getActiveSpreadsheet(“Totalwork”);
// var totaldata = TotalsSheet();
// for (var i = 0; i < totaldata.length; i++) {
// All.appendRow(totaldata[i]);
// } 
//}
// — — — — — — — — — — — — — — — — — — — — — — — — — 
/**
 * Deletes rows in the active spreadsheet that contain 0 or
 * a blank valuein column “C”. 
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
 // ↓下面這個function可以將指定欄位內出現指定關鍵字的行刪除(用來清除空行也可以)
function readRows() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var rows = sheet.getDataRange();
 var numRows = rows.getNumRows();
 var values = rows.getValues();
var rowsDeleted = 0;
 for (var i = 0; i <= numRows — 1; i++) {
 var row = values[i];
 // ↓5 = 指定的欄位，整個判斷的意思是如果欄位5內的文字=V or 完成的話這一行就刪除
 if (row[5] == “v” || row[5] == ‘完成’) {
 sheet.deleteRow((parseInt(i)+1) — rowsDeleted);
 rowsDeleted++;
 }
 }
};
// — — — — — — — — — — — — — — — — — — — — — — — — — 
/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet();
 // ↓選單按鈕名稱跟裡面的巨集名稱盒要啟動的函式，巨集跟巨集要用”,”隔開。大概是這種感覺吧{name : “??”,functionName : “???”},{name : “???”,functionName : “???”}
 var entries = [{
 name : “合併儲存格內容(從第2個格資料表往後合併)”,
 functionName : “Combine”
 },{
 name : “移除指定欄位中出現指定詞的行”,
 functionName : “readRows”
 }];
 sheet.addMenu(“巨集”, entries);
};
 // ↓下面這段是為了讓巨集按鈕出現在google試算表裡面，我是從網站上取得的。
function myFunction() {
 var spreadsheet = SpreadsheetApp.getActive();
 var sheet = spreadsheet.getActiveSheet();
 var currentCell = spreadsheet.getCurrentCell();
 sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
 currentCell.activateAsCurrentCell();
 spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

↑完整Code↑：

Step4：重新整理(F5) google試算表的頁面

Step5：按下新出現的巨集按鈕裡面要執行的功能

Step6：大功告成

特殊欄包含特定元素刪除：

假設我們現在要將匯總表中草莓這一欄裡面值包含"5"的或= 10 or 沒東西的行刪除

一樣進到工具中的指令編輯器找到function readRows()

function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    //  ↓5 = 指定的欄位，整個判斷的意思是如果欄位5內的文字=V or 完成的話這一行就刪除
    if (row[5] == "v" || row[5] == '完成') {
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

將這一行

if (row[5] == "v" || row[5] == '完成') {

改成以下

if (row[1]=="10"||row[1]==''||row[1].toString().match(RegExp(/5/))) {

||：或、or的意思

row[1]：草莓=第2欄，編號就是1（因為從0開始編碼）

=="10"：10=要刪除行的指定數值or文字。如果這樣寫，要這個儲存格內的文字與等於後值一模一樣才會判定是True。

.toString().match(RegExp(/5/)：如果要判斷儲存格內是否包含特定字元(比如儲存格 = #AA 數字數字數字，而你只要判斷最前面的字母是否為AA)，則要先將儲存格內容要先將內容轉成str，再使用match函數判斷裡面是否有包含特定字元。match裡面的RegExp(/5/)為正則表達式，兩個/斜槓代表開始以及結束，可以使用正則表達式達成各種搭配(比如包含特定英文字母或數字達到多少位數等等，詳情可看說明)。

如果只是單純需要儲存格內包含特定元素，則只要像下面那樣寫就好了。

row[1].toString().match("5")

然後存檔，重整google試算表，選擇第二個

大功告成~