Google Sheets API，在 Google Apps Script 上的好用部份

本篇要解決的問題

概念：試算表 工作表

var ss = SpreadsheetApp.getActiveSpreadsheet();

建立模擬資料

試算表好用函式

const s = SpreadsheetApp.getActiveSpreadsheet();

取得試算表的 ID

文件：getId()

試算表的 ID 可以直接從網址上看到，也可以用函式取得。

function getId() {

  const id = s.getId();

  Logger.log(id)  

}

透過 GAS 的執行功能，會看到如下結果：

---

取得試算表的名稱

文件：getName()

取得試算表的檔案名稱。

function getName() {

  const name = s.getName();

  Logger.log(name)  

}

---

取得所有工作表

文件：getSheets()

取得試算表下的所有工作表，取出來後要用迴圈再來取得工作表的資訊，範例中是用 getName() 來取得每張工作表的名稱。

function getSheets() {

  const sheets = s.getSheets();

  if (sheets.length > 1) {

    for(let ss of sheets) {

      Logger.log(ss.getName())

    }

  }

}

---

新增工作表

文件：insertSheet(sheetName)

新增的工作表，位置會插入在執行中的工作表之後。

比方我們目前在執行中的工作表是「測試表1」，那新增的工作表就會插在測試表1之後。

function insertSheet() {

  s.insertSheet('測試新增工作表');

}

執行後：

---

刪除工作表

文件：deleteSheet(sheet)

刪除指定的工作表，抓到工作表就可以刪除，範例示範的是刪除指定的工作表名稱。

function deleteSheet() {

  const sheet = s.getSheetByName('測試新增工作表');

  s.deleteSheet(sheet);

}

---

試算表加上客製選單

文件：addMenu(name, subMenus)

這個功能蠻有趣的，就是可以在 Sheets 的導覽列上加上我們想要的選項。

也可以設定點了我們新增的選項後要執行哪個 function。

function addMenu() {

  const menu = [];

  // name：項目名稱。functionName：點擊時執行哪個 function

  menu.push({ name: '新增工作表', functionName: 'insertSheet' });

  menu.push(null); // 分隔線

  menu.push({ name: '刪除工作表', functionName: 'deleteSheet' });

  s.addMenu("加上客製選單", menu);

}

// 試算表打開時執行

function onOpen() {

  addMenu();

}

打開試算表，執行完 addMenu 後就會看見導覽列上多了一個選項：

---

更新試算表的客製選單

文件：updateMenu(name, subMenus)

這跟新增客製選單很像，要注意的是，如果原本就有加上客製選單，再用更新選單時，選單名稱相同會覆蓋，不同的才會新增。

// 選單名稱相同會覆蓋原本 addMenu 時新增的

function updateMenu_update() {

  const menu = [];

  menu.push({name: '更新成只有新增工作表', functionName: 'insertSheet'});

  s.updateMenu('加上客製選單', menu);

}

// 選單名稱不同，就會新增一個客製按鈕

function updateMenu_create() {

  const menu = [];

  menu.push({name: '來啊再新增一個加入工作表', functionName: 'insertSheet'});

  menu.push(null); // 分隔線

  menu.push({name: '來啊再新增一個刪除工作表', functionName: 'deleteSheet'});

  s.updateMenu('加上客製選單2', menu);

}

因為一次實作了覆蓋跟新增，原本結果部份錄成影片的方式呈現，結果 YouTube 不給傳，最後還刪掉了影片，就算了，就請大家自行貼上程式碼後測試囉~

---

右下角彈出視窗

文件：toast(msg, title)

Toast 就是指出現在畫面上的一個小視窗，通常會有時間性，比方三秒或五秒後自動消失。

function toast() {

  s.toast("Let's Write - 這是一個 Toast 的內文部份。", "開啟一個 Toast");

}

Sheets 上的 Toast 長這樣：

---

工作表好用函式

從名稱找工作表

文件：getSheetByName(name)

範例中是抓出工作表，並且 Log 出該工作表是排序第幾。

function getSheetByName() {

  const sheet = s.getSheetByName('測試表1');

  if (sheet != null) {

    Logger.log(sheet.getIndex());

  }

}

---

取得工作表的名稱

文件：getSheetName()

範例中是先用 getSheets()[0] 取出第一張工作表，然抓再取出工作表名稱。

function getSheetName() {

  const sheet = s.getSheets()[0];

  Logger.log(sheet.getSheetName());

}

---

取得工作表的值

文件：getSheetValues(startRow, startColumn, numRows, numColumns)

範例中提供了二種抓工作表所有值的方法：getSheetValues、getRange。

function 中給的參數都一樣：

• startRow：第幾列開始
• startColumn：第幾欄開始
• numRows：共要抓幾列的值
• numColumns：共要抓幾欄的值

下列範例程式碼寫：1, 1, 6, 2，意思就是從第 1 列、第 1 欄開始，抓 6 * 2 的資料。

function getSheetValues() {

  const sheet = s.getSheets()[0];

  // 方法 1：getSheetValues

  const values1 = sheet.getSheetValues(1, 1, 6, 2);

  Logger.log(values1);

  // 方法 2：getRange

  const range = sheet.getRange(1, 1, 6, 2);

  values2 = range.getValues();

  Logger.log(values2);

}

---

清空工作表的值

文件：clear

這個就不示範了，因為一用會整張工作表的值都被清空。

function clear() {

  const first = s.getSheetByName('測試表1');

  first.clear();

}

預設的清除，除了資料外連原本設定的格式也會被清空，比方今天我們設定了條件式格式突顯出重複的值，一用了 clear，那設定的條件式格式也會被清掉，就變成每次都要重新設定。

Sheet 很貼心的給了參數來選擇 clear 是要清格式、清資料，還是二個都清。

formatOnly：清格式。

contentsOnly：清資料。

function clear() {

  const first = s.getSheetByName('測試表1');

  first.clear({ formatOnly: false, contentsOnly: true });

}

另外補充一個，條件式格式突顯重複值的公式為：

=countif($A:$A,$A1)>1

---

工作表最底部新增一列值

文件：appendRow(rowContents)

這個 function 好用，要塞資料到 Sheet，就寫一個迴圈不斷用 appendRow 就對了。

function appendRow() {

  const sheet = s.getSheets()[0];

  sheet.appendRow(['ID', '姓名', 'email', '電話']);

}

---

取得最後有值的列是第幾列

文件：getLastRow()

比方我們的 Demo 共有 6 列資料，用 getLastRow 就會回傳 6。

function getLastRow() {

  const sheet = s.getSheets()[0];

  const lastRow = sheet.getLastRow();

  Logger.log(lastRow);

}

---

取得最後有值的欄是第幾欄

文件：getLastColumn()

比方我們的 Demo 共有 4 列資料，用 getLastColumn 就會回傳 4。

function getLastColumn() {

  const sheet = s.getSheets()[0];

  const lastCol = sheet.getLastColumn();

  Logger.log(lastCol);

}

---

結合應用，直接取整張工作表的值

這邊結合上面三個 function，可以不用手動輸入要抓幾列幾欄，就一次抓工作表裡的資料。

function getSheetVal() {

  const sheet = s.getSheets()[0];

  const lastRow = sheet.getLastRow();

  const lastColumn = sheet.getLastColumn();

  const values = sheet.getSheetValues(1, 1, lastRow, lastColumn);

  Logger.log(values);

}

---

原始碼