DEV Community 👩‍💻👨‍💻

Cover image for Google Sheets API,在 Google Apps Script 上的好用部份
Let's Write
Let's Write

Posted on • Originally published at letswrite.tw

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

本篇要解決的問題

之前寫過蠻多篇關於 Google Sheets 的應用,卻一直沒仔細的看過官方文件。取值、寫值靠的是別種的方法而不是原有的 Google Apps Script 的內建函式。

最近因為一些事情,覺得將來可能有機會拿 Google Sheet 當一個小資料庫來用,就認真的閱讀了文件,把認為會用到的一些函式給存下來,未來如果機會成熟了,就可以回頭來看這篇使用。

本篇是 August 看過了一遍 Google Apps Script 關於 Google Sheets 的文件後,選出的幾個好用函式,實際使用看效果並存下來,並沒有包含所有文件上提到的內容。

以下提到 Google Apps Script 的部份簡稱 GAS,提到 Google Sheets 的部份簡稱 Sheets。

因為本篇主要是用 GAS 來讀寫 Sheets,所以原始碼這次就不存 GitHub 上,直接存在 GAS 上,在這邊先提供給大家:

https://script.google.com/d/1JZfC44ra8NIWAXdsgNBMrg5ilG2_r_xwdpW7PaDMuUTiBM6Sf8Iw9g9K/edit?usp=sharing


概念:試算表 > 工作表

這邊先說明一個基本概念,我們一般會說「你那個 Excel 的檔案整理好後寄給我」,我們講 Excel 時腦子裡想到的會是 Excel === 試算表 === 工作表 === Google Sheets。

實際上看過官方文件後,就會理解到一個 Google Sheets 的檔案,就是「試算表」(Spreadsheet),而一個試算表裡我們會開有很多張表,那些表就是「工作表」(Sheet)。

看文件會看見命變數時,第一行常常會是:

var ss = SpreadsheetApp.getActiveSpreadsheet();
Enter fullscreen mode Exit fullscreen mode

就是在命這整個試算表本身。


建立模擬資料

為了可以實際操作看效果,August 有建立了一個 Demo 用的試算表,裡面的資料都是用以前寫的這篇「如何用 Postman Mock Server 快速建立 API Server」建立的。

Demo 用的試算表內容如下:

https://docs.google.com/spreadsheets/d/1FTMg3DkfgSvx3a71A2w08pKZ7zrBb1t6T-R1L_CIlss/edit?usp=sharing

裡面有二張工作表:測試表1、測試表2。


試算表好用函式

以下程式碼中的變數 s,都是指試算表本身:

const s = SpreadsheetApp.getActiveSpreadsheet();
Enter fullscreen mode Exit fullscreen mode

取得試算表的 ID

文件:getId()

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

function getId() {
  const id = s.getId();
  Logger.log(id)  
}
Enter fullscreen mode Exit fullscreen mode

透過 GAS 的執行功能,會看到如下結果:

取得試算表的 ID getId


取得試算表的名稱

文件:getName()

取得試算表的檔案名稱。

function getName() {
  const name = s.getName();
  Logger.log(name)  
}
Enter fullscreen mode Exit fullscreen mode

取得試算表的名稱 getName


取得所有工作表

文件:getSheets()

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

function getSheets() {
  const sheets = s.getSheets();
  if (sheets.length > 1) {
    for(let ss of sheets) {
      Logger.log(ss.getName())
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

取得所有工作表 getSheets


新增工作表

文件:insertSheet(sheetName)

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

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

function insertSheet() {
  s.insertSheet('測試新增工作表');
}
Enter fullscreen mode Exit fullscreen mode

新增工作表 insertSheet

執行後:

插入在執行中的工作表之後


刪除工作表

文件:deleteSheet(sheet)

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

function deleteSheet() {
  const sheet = s.getSheetByName('測試新增工作表');
  s.deleteSheet(sheet);
}
Enter fullscreen mode Exit fullscreen mode

刪除工作表 deleteSheet


試算表加上客製選單

文件: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();
}
Enter fullscreen mode Exit fullscreen mode

打開試算表,執行完 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);
}
Enter fullscreen mode Exit fullscreen mode

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


右下角彈出視窗

文件:toast(msg, title)

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

function toast() {
  s.toast("Let's Write - 這是一個 Toast 的內文部份。", "開啟一個 Toast");
}
Enter fullscreen mode Exit fullscreen mode

Sheets 上的 Toast 長這樣:

右下角彈出視窗 toast


工作表好用函式

從名稱找工作表

文件:getSheetByName(name)

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

function getSheetByName() {
  const sheet = s.getSheetByName('測試表1');
  if (sheet != null) {
    Logger.log(sheet.getIndex());
  }
}
Enter fullscreen mode Exit fullscreen mode

從名稱找工作表 getSheetByName


取得工作表的名稱

文件:getSheetName()

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

function getSheetName() {
  const sheet = s.getSheets()[0];
  Logger.log(sheet.getSheetName());
}
Enter fullscreen mode Exit fullscreen mode

取得工作表的名稱 getSheetName


取得工作表的值

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

範例中提供了二種抓工作表所有值的方法:getSheetValuesgetRange

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);
}
Enter fullscreen mode Exit fullscreen mode

取得工作表的值 getSheetValues、getRange


清空工作表的值

文件:clear()

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

function clear() {
  const first = s.getSheetByName('測試表1');
  first.clear();
}
Enter fullscreen mode Exit fullscreen mode

工作表最底部新增一列值

文件:appendRow(rowContents)

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

function appendRow() {
  const sheet = s.getSheets()[0];
  sheet.appendRow(['ID', '姓名', 'email', '電話']);
}
Enter fullscreen mode Exit fullscreen mode

工作表最底部新增一列值 appendRow


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

文件:getLastRow()

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

function getLastRow() {
  const sheet = s.getSheets()[0];
  const lastRow = sheet.getLastRow();
  Logger.log(lastRow);
}
Enter fullscreen mode Exit fullscreen mode

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


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

文件:getLastColumn()

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

function getLastColumn() {
  const sheet = s.getSheets()[0];
  const lastCol = sheet.getLastColumn();
  Logger.log(lastCol);
}
Enter fullscreen mode Exit fullscreen mode

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


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

這邊結合上面三個 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);
}
Enter fullscreen mode Exit fullscreen mode

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


原始碼

在放在 Google Apps Script 上,請自行點選檔案切換觀看:

https://script.google.com/d/1JZfC44ra8NIWAXdsgNBMrg5ilG2_r_xwdpW7PaDMuUTiBM6Sf8Iw9g9K/edit?usp=sharing

Top comments (0)

DEV

Thank you.

 
Thanks for visiting DEV, we’ve worked really hard to cultivate this great community and would love to have you join us. If you’d like to create an account, you can sign up here.