第一次用Gemini 1.5寫Google Apps Script以進行Google 試算表的自動化

 

為了自動列出雲端硬碟上某個資料夾的使用者，問了ChatGPT一個問題：

我有一個google試算表。試算表的B1儲存格存放的資料是一個google雲端硬碟上的資料夾的連結，例如：
https://drive.google.com/drive/u/0/folders/XXXXXXXXXXXXXXXXXXXX
我需要一個script來完成以下工作：

1. 先清除B2:B100的資料。
2. 在B2顯示資料夾名稱。
3. 在B3顯示資料夾所有者。
4. 在B4、B5、B6...列出資料夾編輯者。
5. 列完編輯者之後，接著再列出檢視者。 

ChatGPT給的程式碼執行後一直出現錯誤，無法順利執行，再三反饋給ChatGPT，多次修改後仍無效，放棄。

想到Google幾天前啟用的Gemini 1.5，以下是Gemini的答案與修改過程：

好的，以下是一個可以完成您需求的 Google Apps Script：

```
function getFolderInfo() {
  // 清除 B2:B100 的資料
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('B2:B100').clearValues();

  // 取得資料夾 ID
  var folderId = sheet.getRange('B1').getValue().match(/folders\/(.*)/)[1];

  // 取得資料夾資訊
  var folder = DriveApp.getFolderById(folderId);

  // 顯示資料夾名稱
  sheet.getRange('B2').setValue(folder.getName());

  // 顯示資料夾所有者
  sheet.getRange('B3').setValue(folder.getOwner().getEmail());

  // 顯示資料夾編輯者
  var editors = folder.getEditors();
  for (var i = 0; i < editors.length; i++) {
    sheet.getRange('B' + (4 + i)).setValue(editors[i].getEmail());
  }

  // 顯示資料夾檢視者
  var viewers = folder.getViewers();
  for (var i = 0; i < viewers.length; i++) {
    sheet.getRange('B' + (4 + editors.length + i)).setValue(viewers[i].getEmail());
  }
}
```

這個 Script 的執行方式如下：

1. 在 Google 試算表中，開啟 Script 編輯器。
2. 將上述程式碼貼到 Script 編輯器中。
3. 儲存 Script。
4. 在 Script 編輯器中，點選執行按鈕。

執行 Script 後，試算表中的 B2:B100 儲存格就會顯示出您指定的資料夾的相關資訊。

以下是 Script 的詳細說明：

* `getFolderInfo()` 函式是 Script 的入口點。
* `sheet.getRange('B2:B100').clearValues()` 這一行用於清除 B2:B100 儲存格中的資料。
* `var folderId = sheet.getRange('B1').getValue().match(/folders\/(.*)/)[1]` 這一行用於取得資料夾 ID。
* `var folder = DriveApp.getFolderById(folderId)` 這一行用於取得資料夾物件。
* `sheet.getRange('B2').setValue(folder.getName())` 這一行用於顯示資料夾名稱。
* `sheet.getRange('B3').setValue(folder.getOwner().getEmail())` 這一行用於顯示資料夾所有者。
* `var editors = folder.getEditors()` 這一行用於取得資料夾編輯者陣列。
* `for (var i = 0; i < editors.length; i++) { sheet.getRange('B' + (4 + i)).setValue(editors[i].getEmail()) }` 這一行用於顯示資料夾編輯者。
* `var viewers = folder.getViewers()` 這一行用於取得資料夾檢視者陣列。
* `for (var i = 0; i < viewers.length; i++) { sheet.getRange('B' + (4 + editors.length + i)).setValue(viewers[i].getEmail()) }` 這一行用於顯示資料夾檢視者。

希望這個 Script 能夠滿足您的需求。

我：執行後出現錯誤訊息：TypeError: sheet.getRange(...).clearValues is not a function

Gemini：

出現這個錯誤訊息的原因是，您使用的 Google Apps Script 版本太舊了。在舊版本的 Google Apps Script 中，`sheet.getRange()` 方法沒有 `clearValues()` 函式。

要解決這個問題，您需要將 Google Apps Script 版本更新到最新版本。更新方法如下：

1. 在 Google 試算表中，開啟 Script 編輯器。
2. 點選 **資源** > **管理 Script 版本**。
3. 在 **版本** 對話方塊中，點選 **更新**。

更新 Google Apps Script 版本後，您應該就可以正常執行 Script 了。

如果您不想更新 Google Apps Script 版本，也可以使用以下方法來清除 B2:B100 儲存格中的資料：

```
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange('B2:B100');
range.clearContent();
```

`clearContent()` 方法的效果與 `clearValues()` 方法相同，但它適用於所有版本的 Google Apps Script。

採用Gemini提供的第二個方案，修改程式碼之後就能順利執行了，不虧是自家產品，對自家的試算表有較好的支援。

相較於GhatGPT，Gemini第二次回覆就成功了，而且每段程式碼還一一解釋，非常適合學習電腦語言，超棒der～～～

 

超級無敵好用，今天與ChatGPT的對話

使用chatGPT3.5協助電腦工作已經有一陣子了，心得就一個字：神！大概是我用過最厲害的應用，每一次GPT寫的代碼全都能滿願，讓我這個程式小白也能享受一點執行程式的樂趣，真的是太棒了！今天的對話如下：

Me: 在google試算表的script中，我有一段程式碼如下：

function hideDataRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getRange("D2:D" + sheet.getLastRow());
   // 假設資料從第二列開始，D欄
  var dataValues = dataRange.getValues();
  for (var i = 0; i < dataValues.length; i++) {
    var row = i + 2;
     // 資料索引 + 2，因為索引從0開始，而實際的列從2開始
    var rowData = dataValues[i][0];
    if (rowData !== "") {
      sheet.hideRows(row);
    }

  }
}

我想在程式碼的開頭加入執行的條件：如果G1儲存格內的字串包含"ok"，那麼就繼續往下執行，否則就停止執行。該怎麼改寫呢？

GPT秒答：

您可以在程式碼的開頭加入一個條件判斷，檢查 G1 儲存格的值是否包含 "ok"。如果不包含，則停止執行，否則繼續執行。以下是修改後的程式碼：

function hideDataRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // 檢查 G1 儲存格的值是否包含 "ok"
  var conditionCell = sheet.getRange("G1");
  var conditionValue = conditionCell.getValue();
  
  if (conditionValue.indexOf("ok") === -1) {
    // 如果 G1 儲存格的值不包含 "ok"，則停止執行
    return;
  }
  
  var dataRange = sheet.getRange("D2:D" + sheet.getLastRow());
  var dataValues = dataRange.getValues();
  
  for (var i = 0; i < dataValues.length; i++) {
    var row = i + 2;
    var rowData = dataValues[i][0];
    
    if (rowData !== "") {
      sheet.hideRows(row);
    }
  }
}

這樣修改後，當 G1 儲存格的值不包含 "ok" 時，程式碼將會在條件判斷處停止執行，否則將繼續執行隱藏資料列的操作。

以下是其他chatGPT成功幫我解決的問題（皆列出原始提示）： 

我有一個google sheets工作表，A欄是日期，如果日期是今天以前的日期，且迄今已超過7天，就隱藏該列，請問script應該怎麼寫？

我想在google試算表中寫一個script，讓我可以隱藏工作表C欄中除了第一列之外，所有包含資料的列，程式碼應該怎麼寫呢？

淺談Google雲端硬碟管理（一）權限管理

前言

在公司、公部門或NGO內部進行團隊協作時，有時為了省下架設主機與購買軟體的費用，會利用現成的Google雲端硬碟來管理資料，不論是使用PC瀏覽器還是安卓手機預裝的App，都能透過網路檢視與編輯線上檔案，十分便利。對於有這方面需求的讀者，這裡就整理一些管理雲端硬碟的要點，由於文長，將分權限管理、資訊安全與資料輸入輸出等三方面來談。

一、權限管理

1. 公用帳號（管理員帳號）的必要性

在雲端硬碟上，為了讓不同職級或不同單位的使用者只能存取自己權責範圍內的檔案，必須管控檔案的權限，但如果讓使用者各自在雲端建立或上傳檔案，那麼使用者就會成為檔案擁有者，等同獲得該檔的最高控制權，其結果就是使用者能在沒有監管的情況下，讓任何人存取自己所擁有的檔案。所以團隊利用免費的「非企業版」雲端硬碟協作時，要先建一個公用帳號，而且最好所有放在雲端上共用的檔案或資料夾，都統一由公用帳號擁有（具有最高控制權），這樣才能談權限管理。

此外，如果用一個私人帳號來管理雲端硬碟上的所有檔案，雖然也能避免上述問題，但將來卸任移交時，勢必面臨很大的困難。因為不像PC電腦上的檔案，只要複製到隨身碟丟給新人就好，在雲端硬碟上，必須先轉移每個檔案與資料夾的擁有權給新的管理者，然後再關閉自己的權限，工程不算小。如果一開始就使用公用帳號的話，交接時只要將公用帳號及密碼交給新人，連複製檔案都免了，省事許多。

2. 建立樹狀結構的資料夾與檔案

當雲端硬碟上的檔案很多時，為了讓檔案更有組織，畫面也不會充斥太多項目，跟在PC端一樣都會使用樹狀結構的資料夾來存放檔案。另外由於在管理權限的時候，原則上都會在資料夾上設定共用權限（如此這個資料夾所包含的子資料夾與檔案，以及日後在這個資料夾之中新增的項目，都會繼承這個資料夾的權限，不必逐一設定），所以建立樹狀目錄時，會優先考量不同單位或不同職級，例如相同單位或相同職級會用到的檔案會放在同一個資料夾，以利權限設定及管理。

另外，如果您的組織中也有其他部門在使用google雲端硬碟，為這些部門的共同主管建立一個自己部門的根目錄是必要的，這樣主管看到的畫面才不會充斥大量資料夾與檔案，而是只會看到各部門的根目錄。而主管的檔案存取權，只要在根目錄上設定一次就好，不用每個資料夾一一設定才能讓主管看到所有內容。舉例說明：

假設有10個資料夾要開權限給主管看，如果在每個資料夾一一設定共用權限，除了低效，主管在雲端硬碟上也會看到10個資料夾，因此比較合理的作法是先設一個根目錄，在根目錄上開權限給主管，再把10個資料夾移動到根目錄之中，如此這10個資料夾就能繼承來自上層資料夾的權限設定（主管都能看到），此外主管也只會看到一個根目錄，當他需要進一步查閱下層的10個子資料夾時，再點開根目錄即可，這樣主管的電腦畫面就會清爽許多。

反之來看不設根目錄的後果。如果主管轄下有10個單位，每個單位都開10個資料夾給主管看，那麼主管就會看到100個資料夾，這將造成主管的電腦畫面太過零亂，更無法區分多個都叫「月報」的資料夾，到底哪個是屬於哪個部門？因此很難找到他想看的內容。

按照上述的原理，假設主要的樹狀目錄已經建立好了，那麼開設權限的合理順序應該是：

A.在根目錄上開權限給大主管。
B.在根目錄之下的資料夾上逐級開權限給各級幹部及使用者。
C.對於一些不同單位或不同群組都會用到的檔案，應使用「捷徑」（拖動檔案時，同時按住ctrl再移動到目標資料夾，即可建立該檔的捷徑），使同一檔案能出現在不同單位的資料夾。
D.最後對少數個案，可直接在檔案上設定權限。

如果一開始沒按以上順序施工，導致主管與各級幹部看到的畫面凌亂不堪，還是有補救的方法，那就是趁連續假期大家不用雲端硬碟時，關閉所有檔案與資料夾的共用權限（請善用進階搜尋功能來列出公用帳號擁有的所有檔案），然後從根目錄開始逐層重新設權限即可解決。


3. 禁止編輯者自行開放檔案權限

Google雲端硬碟非企業版的一個大bug是：擁有檔案編輯權限的使用者，在google雲端硬碟的預設值下，能自行開放編輯權限或檢視權限給更多使用者。

設想一個極端的例子：雲端硬碟管理者開放了某個檔案的編輯權給使用者A，A又自行開放編輯權給B, C, D， BCD三位又各自開放編輯權給更多人，到最後檔案幾乎等於在網上裸奔—給不該看的人看光光，所以這個預設功能必須在開放檔案共用權限時一併禁用，操作方式如下：

4.選任合適的管理員

如同一家公司的執行長不會親自去管理公司內的電腦，而會授權給資訊管理部門一樣，這裡也不建議主管親自下場管理雲端硬碟，而是交給合適的人來管理，適任的管理員條件如下：

A. 有電腦、能上網、會打字只是最基本的條件。管理者還必須比所有人員更熟悉電腦的軟硬體，才能同時解決主管與使用者的需求。至少要同時使用Chrome、Edge、Firefox以同時登入私人帳號與公用帳號，要會Anydesk或Teamviewer以便遠端處理使用者的電腦問題。

B. 年齡不宜太大。雖然年齡不代表電腦能力，但從學生時代就接觸電腦，跟出社會再自學電腦，電腦能力大概還是會有相當落差的，特別是曾在學校被指導教授操過幾年...🤣

C. 管理員的電腦不能太舊，太舊的硬體不支援較新的作業系統，例如微軟已放生Win 7，資安漏洞就無法得到即時修補。話說回來，真正會用電腦的人，通常也無法忍受舊電腦或小螢幕的低效。

以上是必要條件，後面三個條件是加分項：

D. 如果有管理大量資料的需求，管理者最好學過資料庫軟體如Access、SQL，能掌握資料庫設計原則，如果一開始存放資料的表格設計不良，往後資料的處理很容易窒礙重重，修改成本也會很大。

E. 如果有自動統計KPI或輸出月報的需求，管理者應熟悉excel函數（與大部分google sheets函數通用），至少要會20～30個，if、sumifs、countifs、filter、unique、sort、xlookup、indirect、join、transpose、year、month、regularextract、importrange等函數語法都是基本功，還必須會用"函數（函數（函數（函數）））"這種套娃娃的語法，才能把試算表的資料處理效率開到最高。

一個典型的「套娃娃」，數數看套了幾個娃？

F. 除了函數，Google試算表還能用程式碼自訂更強大的自動化功能，類似excel巨集，只不過其程式語言是使用javascript而非VBA，但會電腦語言的人比會excel函數的人更稀缺就是了😅。

總之，雲端硬碟的權限與其中的資料管理最好放權給適任者才能事半功倍，主管也才能集中精力在團隊的資源整合與溝通協調之上，不用在龐雜瑣碎的電腦作業上勞神。


5. 使用者人數與管理成本

雲端硬碟的權限管理員只要一人就夠了，比較不會出現多頭馬車或者人人都無法掌握全局的情況，但雲端硬碟上的檔案往往會開放給數位使用者參與編輯，雖然這就是我們當初使用雲端硬碟的本意—檔案可由多人共同編輯，減少檔案傳遞與整併的時間。然而，由於使用者會在編輯檔案時製造各種意想不到的錯誤，因此使用者人數越多，出錯的風險越高，管理成本也就越高，所以到底要開放給多少人共用？這是一門藝術，沒有標準，只能酌情。

在多人使用雲端硬碟輸入資料時，這裡針對幾種不同的情況提出建議：

A.若團隊中大多數人不會電腦、沒有電腦...

因為全面電腦化的硬體建設成本與人員培訓成本是極高的，在這種情況下處理資料時可以全部採用紙本表格，手寫填好後再將表格集中交由少數會電腦的人上雲端硬碟登打資料，這是效率最高也是管理成本最低的辦法，也因為user最少，雲端硬碟管理員最輕鬆，只不過使用者人數少，每人要分攤的電腦作業量會較多。

雖然現在人人都有手機，也能直接用手機編輯雲端硬碟的檔案，但仍不建議用手機APP或手機的Google表單來輸入資料，因為電腦作業的效率與「顯示面積」高度相關。使用一台27寸大螢幕，因顯示面積大，所以作業效率高，手機的根本問題正是顯示面積太小，在相同字體大小下，一張A4紙本表格能同時顯示的內容還比手機多好幾倍，因此用手機代替電腦，不如回歸最純樸的A4紙表。而且手機還有字太小傷眼，中文打字慢，容易手滑誤操作等問題。

B. 若團隊中每個單位至少有一個人會電腦、有電腦...

此時雲端硬碟可以開放給較多人使用，不至於出現太多問題，但若人員電腦能力良窳不一，初期還是有必要投入成本進行人員培訓的。

C. 團隊成員都是電腦高手

此時使用者在編輯檔案時出錯的風險很低，權限可安心開，同時可以減少一些紙本表格。但紙本表格在一些情況還是有必要的，特別是外勤不方便用電腦時，紙筆還是非常有用且高效的資訊紀錄工具，至少本人的經驗是：在使用中文的情況下，用筆寫字會比用手機打字還快，且輕鬆，但現在年輕人用手機打字好像都很快，那就另當別論了。

（待續）

用image函數在google試算表中顯示報名者上傳到google表單中的照片或大頭照

舉辦活動時，利用google表單傳送報名資料已經成了一種趨勢，不僅省時也非常環保，有時候我們還可要求報名者上傳證件大頭照，當然這難不倒google表單，但我們收到的報名資料在試算表中會是這個樣子：

沒辦法一眼看到報名者上傳的圖檔，只有圖檔連結。如果要看到照片的話，要將滑鼠移動到連結上懸停，才會跑出報名者的大頭照：

這樣很不直觀，也不能把照片列印出來上呈，這時候就要使用image這個函數了。步驟如下：

1. 首先將存放上傳照片的資料夾權限開啟成「知道連結的使用者」：

2. 將「表單回應」複製成一般工作表：

3. 在複製好的工作表中，新增用來顯示照片的D欄：

4. 在D2儲存格中貼上以下函數：

=image(concatenate("http://drive.google.com/uc?export=view&id=",RIGHT(C2,33)),1)

image函數的語法是：IMAGE(網址, [模式], [高度], [寬度]) 

這裡用到的參數是[模式]，1是依照原圖比例顯示，2是將照片拉伸並填滿儲存格。通常大頭照我們都不希望人像比例變形，所以使用1。D2貼上函數後，應該就可以看到照片跑出來了，將D2函數複製到最後一列，即可將所有報名者的大頭照在D欄顯示出來：

照片很小張，沒關係，最後再加大列高與欄寬，就可放大顯示全部大頭照。

google試算表獨家函數(excel不會)—regexextract抽取字串中的數字

在處理資料的時候，有時必須抽取出字串中的數字部份，如果資料格式整齊一致，在excel中可以用len、left、right、search等簡單函數處理。

但如果資料是這樣：
105
A50
BB7
CCC125
42
7

那麼就需要使用excel的陣列公式，例如這個教學。不過陣列公式並不容易理解，寫起來不但傷腦筋還有限制。但如果使用google試算表，那麼使用regexextract這個獨家函數(regular expression extract的縮寫)配合正規表示式，寫起來就非常直觀了。

假設有以下影集名稱資料，需要抽取其中的集數：

資料非常不整齊，唯一可以用的規律是集數皆為阿拉伯數字，如果用excel的陣列公式會很難寫。先把資料貼到google試算表，並使用公式如下：

這個公式中，[0-9]+是正則表達式，可以辨識字串中第一次出現的數字，輸出結果如上例B欄，其格式預設為文字，所以會保留數字前面的0。如果需轉為數字格式，則在公式後多寫+0即可：

輸出如C欄所示。結果可貼回excel。

除了抽取數字，這個函數的用途應該很廣，只要是可以用正則表達式描述的字串，例如英文字母、電話號碼、身份證字號等等，皆可用regexextract提取，讀者可自行試驗。