112年 - Google Apps Script雲端程式設計實務班

tags: `Google Apps Script` `產業人才投資方案` `授課資料`

此項筆記供112年產業人才投資方案-Google Apps Script雲端程式設計實務班課程使用，講師會將上課補充重點、參考資料與程式碼留在此頁面 :books:

上課地點：崑山科技大學民生應用學院二館 H2605教室 (電競教室)
授課教師：鄭郁翰講師 [前往教師簡介]
課程大綱：https://cee.ksu.edu.tw/2766

本課程使用之輔助教材：Google Apps Script雲端自動化與動態網頁實戰(第二版)
書籍影音教學、範例程式檔下載：http://books.gotop.com.tw/download/ACU084300

:memo: 課前準備

Step 1: 上課前應準備的

完成 Google 帳號申請
安裝 Google Chrome 瀏覽器

:pushpin: 因為是使用 Google 的雲端服務，所以使用 Google Chrome 瀏覽器具有最佳體驗

Step 2: 課前能力檢核

Excel使用經驗 - 我曾使用過各項函數完成我的工作
Excel使用經驗 - 我曾使用過合併列印來產生大量Word檔案
Excel使用經驗 - 我曾使用過VBA來撰寫巨集程式
Google雲端服務使用經驗 - 我曾使用過Google試算表來處理工作
Google雲端服務使用經驗 - 我曾使用過Google雲端硬碟分享檔案
我具有程式設計的能力或實際工作經驗
我具有網頁設計的經驗，理解HTML、CSS的架構
我具有網頁設計的經驗，理解JavaSript程式語言
我具有實際使用過 Google Apps Script (GAS) 的經驗

HTML參考資料:
https://developer.mozilla.org/zh-TW/docs/Learn/Getting_started_with_the_web/HTML_basics

一、Google 試算表

網路共用的建議：如果希望試算表不公開，只提供特定人士存取，應該採個別授權方式。
參考資料：https://www.isecurity.com.tw/news-and-events/20200611-accidental-exposure-in-google-link-sharing/
Google試算表提供的函數大多數與Microsoft Excel相同，更完整的函數可參考Google網站的參考文件
Google試算表常用函數
https://support.google.com/docs/table/25273?hl=zh-Hant

Google Sheet 的 GAS API

先認識「試算表」的結構，以Microsoft Excel來說，整個檔案就是一個「活頁簿」(workbook)，活頁簿下可以有多個「工作表」(worksheet)，每個表內又有多個「列」(row)或「欄」(column)，以及其下的「儲存格」(cell)，

名稱對照

項目	Microsoft Excel	Google Sheet
活頁簿	Workbook (活頁簿)	Spreadsheet (試算表)
工作表	Worksheet (工作頁)	Sheet (工作表)

GAS開啟試算表

可以使用openById()或openByUrl()
如果是把GAS附加在試算表上，還可以使用getActiveSpreadsheet()來取得目前「試算表」
取得試算表後，就要決定要開啟哪一個「工作表」，試算表物件下有 getSheetByName('name') 以工作表名稱來取得該工作表物件

另外，如果是把GAS附加在試算表上，也可以直接使用getActiveSheet()取得目前試算表中的「第一個工作表」(這樣就可以跳過第3步驟)






function myFunction() {
  var book = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxx/');
  var sheet = book.getSheetByName("工作表1");
  var range = sheet.getRange(2,1)
  Logger.log(range.getValue());
}





function myFunction() {
  var val = SpreadsheetApp.getActiveSheet().getRange(3,1).getValue()
  Logger.log(val);  
}

如何在試算表使用Google翻譯?

直接使用公式中的函數「GOOGLETRANSLATE()」


=GOOGLETRANSLATE(A2,"zh-TW","en")

參考資料：https://support.google.com/docs/answer/3093331?hl=zh-Hant
語系對照表：https://cloud.google.com/translate/docs/languages















function onEdit(e) {
  
  var c = e.range.getColumn()
  var r = e.range.getRow()
  var sheet = SpreadsheetApp.getActiveSheet();

  var cn = LanguageApp.translate(e.value,'zh-TW','zh-CN');
  var en = LanguageApp.translate(e.value,'zh-TW','en');
  var ja = LanguageApp.translate(e.value,'zh-TW','ja');
  
  sheet.getRange(r,c+1).setValue(cn);
  sheet.getRange(r,c+2).setValue(en);
  sheet.getRange(r,c+3).setValue(ja);
}

Google試算表自訂函數

如果希望在試算表輸入公式時會有自動完成的提示功能，要依格式加入註解










/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

https://developers.google.com/apps-script/guides/sheets/functions?hl=zh-tw#naming

輸入參數也可以輸入一段「範圍」，例如A1:B5













/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

https://developers.google.com/apps-script/guides/sheets/functions?hl=zh-tw#naming

無論如何，自訂函式呼叫必須在 30 秒內傳回；否則，儲存格會顯示錯誤訊息：Internal error executing the custom function.

二、Google Apps Script 與網頁應用程式

GAS的網頁應用程式可以接受輸出結果為HTML或JSON格式，寫完程式後存檔，必需使用「部署」功能來發布為網頁應用程式，取得網址後才能使用

如何從GAS顯示資料為HTML?

使用 ContentService.createTextOutput() - 直接輸出文字



function doGet() {
  return ContentService.createTextOutput("Hello World");
}

使用 HtmlService.createHtmlOutput() - 直接輸出HTML



function doGet() {
  return HtmlService.createHtmlOutput('<b>Hello, world!</b>');
}

https://developers.google.com/apps-script/reference/content/content-service

使用 HtmlService.createHtmlOutputFromFile() - 直接使用HTML檔案回傳



function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index');
}

https://developers.google.com/apps-script/guides/html#code.gs
4. 使用 HtmlService.createTemplateFromFile() - 直接使用HTML檔案回傳，並且將其以模板方式設計，可使用<? ... ?> 嵌入GAS程式碼，並且也從GAS端傳入變數，呼叫evaluate()後就會開始執行
Code.js





function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();
}

Index.html









<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    Hello, World! The time is <?= new Date() ?>.
  </body>
</html>

https://developers.google.com/apps-script/reference/html/html-template

練習一：將變數傳遞到HTML端

code.gs






function doGet(e){
  var n = 'Tom';
  var html = HtmlService.createTemplateFromFile('Index');
  html.name = n;
  return html.evaluate();
}

Index.html

















<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>
      通訊錄
    </h1>
    <div>
       查詢時間：<?= new Date() ?>
    </div>
    <div>
       維護人員：<?=name ?>
    </div>
  </body>
</html>






function doGet(e){
  var html = HtmlService.createTemplateFromFile('Index');
  var value = SpreadsheetApp.getActiveSheet().getRange(2,1).getValue();
  html.name = value;
  return html.evaluate();
}

















<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>
      通訊錄
    </h1>
    <div>
       查詢時間：<?= new Date() ?>
    </div>
    <div>
       維護人員：<?=name ?>
    </div>
  </body>
</html>

練習二：結合試算表資料及迴圈呈現結果

指令碼.gs










function doGet(e){
  var html = HtmlService.createTemplateFromFile('Index');

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("工作表1");
  var range = sheet.getDataRange();
  var data = range.getValues();

  html.data = data;
  return html.evaluate();
}





















































<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <style>
        table {
            width: 100%;
            border-collapse: collapse;
            background-color: #f5f5f5; /* 淡灰色背景 */
            border: 2px solid #3498db; /* 淡藍色邊框 */
            color: #333; /* 深灰色文字 */
            font-family: Arial, sans-serif;
            margin-bottom: 20px;
        }

        th, td {
            border: 1px solid #3498db;
            padding: 12px; /* 增加內邊距 */
            text-align: center;
        }

        th {
            background-color: #3498db; /* 淡藍色背景 */
            color: white; /* 白色文字 */
        }

        tr:nth-child(even) {
            background-color: #ecf0f1; /* 淡藍色背景，奇數行 */
        }
    </style>
  </head>
  <body>
    <h1>成績公告</h1>
    <table>    
      <?
        for(var i=0;i<data.length;i++) {
      ?>
      <tr>
        <td><?=data[i][0] ?></td>
        <td><?=data[i][1] ?></td>
        <td><?=data[i][2] ?></td>
        <td><?=data[i][3] ?></td>
        <td><?=data[i][4] ?></td>
        <td><?=data[i][5] ?></td>
      </tr>
      <?
        }
      ?>
    </table>
  </body>
</html>

成果範例：https://docs.google.com/spreadsheets/d/1v39zFLM4RwQ2NuZkqGNhk7vvpNiplToNVLfvxt8xJS0/edit#gid=0

設計具有表單互動的GAS的網頁應用程式

GAS參考資料：https://developers.google.com/apps-script/guides/web
HTML表單介紹 : https://www.fooish.com/html/form.html (網路資料)

程式中主要其中包含 doGet(e) 或 doPost(e) 函式，並回傳HTML內容，可以利用函式中的傳入參數 e 來取得使用者的輸入並與其互動。

傳入參數(e)中常見的成員屬性:

e.queryString
e.parameter
e.parameters
e.pathInfo
e.postData

:exclamation: 請特別注意：在 Google Apps Script 中，參數(parameter)的名稱請不要使用 c 及 sid ，這兩個名稱為系統所保留作為其他用途，請不要在程式中使用這些參數名稱。

如果網頁想要求使用者輸入資料，並由後端接收並處理，可以使用HTML建立表單(Form)後傳到後端處理，但因為GAS的網頁應用程式實際上是執行在iframe框架內，所以設計上稍有差異，請依據以下網址進行設計：https://developers.google.com/apps-script/guides/html/communication#forms

HTML表單(Form)的重要屬性

method：可以設定成GET或POST，分別由GAS的 doGet(e) 及 doPost(e) 處理
name：表單名稱
action：表單資料送往的目的地
onsubmit : 指定表單提交前要執行的JavaScript function

有關 action ，如果要送給目前程式處理，可以簡單使用 ScriptApp.getService().getUrl() 取得目前頁面網址。如果希望在表單中嵌入，可以參考以下方式：




<form method="POST" action="<?= ScriptApp.getService().getUrl() ?>"> 
    姓名：<input type="text" name="name" id="name"  ></input>
         <input type="submit" value="送出" />
</form>

在Google官方文件中，更推薦使用 google.script.run 來達到非同步的函式呼叫(如同AJAX的方式使用)，使用 google.script.run 可以從前端JavaScript程式碼執行指定的後端(.gs)函式。

資料查詢的程式範例

https://docs.google.com/spreadsheets/d/1aHY7BZlcI4_jh8KcEl-9R6wXljVbrtqpnKziLZfEwuE/edit?usp=sharing

其他常見方法：

HtmlOutput.setTitle(title) : 設定網頁標題
HtmlTemplate.evaluate() : 評估回傳的HtmlOutput物件
網頁客戶端也可以使用JavaScript呼叫一些專屬功能，名稱皆以「google.script」開頭，例如「google.script.host.close()」是把目前對話方塊關閉。
– google.script.history 用於GAS的網頁應用程式，
– google.script.host 用於Google服務的側邊欄或對話方塊
– google.script.run 用於非同步執行GAS伺服器端方法時
– google.script.url 可以查詢網址來取得目前的網址參數和片段，只能使用在網頁應用程式
更多基本API參考 : https://developers.google.com/apps-script/reference/html
JavaScript陣列方法: https://www.casper.tw/javascript/2017/06/29/es6-native-array/

成果範例：https://docs.google.com/spreadsheets/d/1-vIUuuOTUKJ-08dzTbVhteqT9kBu4a6iel7KHeZ_e0M/edit?usp=sharing

三、Gmail 電子郵件的應用

在Google Apps Script內建的服務呼叫函式中，分別有 Mail Service 及 Gmail Service 最為常見，其中 Mail Service 這項服務可讓指令碼代表使用者傳送電子郵件。

若與 Gmail Service 比較，Mail Service 只用來做「傳送」電子郵件，無法存取使用者的 Gmail 帳戶；所以，如果需要涉及 Gmail 更完整的控制，例如讀取信件清單這類型的複雜工作，請改採用 Gmail Service 來操作。

:pushpin: 有關電子郵件發送的詳細使用方式及函式清單，可以參考以下網址：
Mail Service : https://developers.google.com/apps-script/reference/mail
Gmail Service : https://developers.google.com/apps-script/reference/gmail?authuser=1

起手式：簡單的電子郵件發送功能

使用 Mail App 發送電子郵件




var subject = "信件標題";
var message = "您好，這裡是信件內容";
var email = "xxx@gmail.com"
MailApp.sendEmail (email, subject, message);

另外一種呼叫方式：







MailApp.sendEmail(
    {
        to: "xxx@gmail.com",
        subject: "信件標題",
        htmlBody: "信件內文<hr />也可以包含HTML語法喔!<br />"
    }
);

此外，也可以使用 Gmail App 來發送電子郵件




var subject = "信件標題";
var message = "您好，這裡是信件內容";
var email = "xxx@gmail.com"
GmailApp.sendEmail (email, subject, message);

:pushpin: 使用 Google Apps Script 發送郵件具有配額限制，如果想知道剩餘多少發送數量，可以呼叫 getRemainingDailyQuota() 這個函式來查詢剩餘額度。

詳細配額限制資訊 : https://developers.google.com/apps-script/guides/services/quotas

發送具有附件的電子郵件

發送電子郵件也可以夾帶附件，可以參考以下兩個官方範例教學
- MailApp : https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)
- GmailApp : https://developers.google.com/apps-script/reference/gmail/gmail-app?authuser=1#sendEmail(String,String,String,Object)







var blob = DriveApp.getFileById("換成你的ID");
var subject = "信件標題(MailApp)";
var message = "您好，這裡是信件內容";
var email = "xxx@xxx.xxx.edu.tw"
MailApp.sendEmail(email, subject, message, {
  attachments: [blob]
});

:pushpin: 請注意：電子郵件夾帶附件也具有相關限制(例如檔案大小)，使用時也要留意

如果檔案放在外部，也可以使用 UrlFetchApp 下載回來，但是 UrlFetchApp 也有配額限制，且下載過程可能需要等待，不建議大量呼叫。


var f = UrlFetchApp.fetch("https://xx.xx/x.jpg").getBlob().setName('檔案名稱');

UrlFetchApp呼叫fetch()後，幾個常見的函數：

getContentText() 取得文字資料
getBlob() 取得二進制大型物件
getContent() 取得原始二進制資料
getResponseCode() 取得HTTP Status Code







  var file = UrlFetchApp.fetch("https://cee.ksu.edu.tw/CourseImage.ashx?id=2517&type=2").getBlob().setName('課程海報'); 
  var subject = "信件標題(MailApp)";
  var message = "您好，這裡是信件內容";
  var email = "xxxx@xxx.tw"
  MailApp.sendEmail(email, subject, message, {
      attachments: [file]
  });

四、Google 表單

:pushpin: 參考資料 : https://developers.google.com/apps-script/reference/forms

可以使用 JSON.stringify() 函式將物件轉換為JSON格式的字串，以利於開發與偵錯。


  Logger.log( JSON.stringify(e) );

可以使用 https://jsonformatter.curiousconcept.com/ 快速格式化JSON內容
Google表單「表單提交時」條件觸發時所傳入參數的格式

當表單提交時，自動發信的程式碼範例







function SendMail(e) {
  var subject = "【活動報名成功通知】" + e.values[2] + "您好，已成功報名活動！" ;
  var message = e.values[2] + "您好，已於" + e.values[0] + "成功報名活動！";
  var email =  e.values[3];
  MailApp.sendEmail (email, subject, message);
}

建立電子郵件的「收信確認」功能






































function SendMail(e) {  
  var random = Math.random().toString(36);

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("表單回應 1");
  sheet.getRange(e.range.rowStart ,5).setValue(random);

  var email =  e.values[1];
  var subject = "【活動報名成功通知】" + e.values[2] + "您好，已成功報名活動！" ;
  var message = e.values[2] + "您好，已於" + e.values[0] + "成功報名活動！請開啟以下網址完成收信確認：" + 
                "https://script.google.com/macros/s/xxxxxxxxxxxxxxxxxxxxxxx/exec?email=" + email + "&id=" + random  ;                 
  MailApp.sendEmail (email, subject, message);
}

function doGet(e){
  var id = e.parameter.id;
  var email =  e.parameter.email;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("表單回應 1"); 
  var data = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();

  var atIndex = data.findIndex(function(item, index, array){
    return item[1] === email && item[4] === id;  
  });

  if(atIndex<0){
     return ContentService.createTextOutput( "驗證失敗");
  }
  else{
    atIndex += 2 ; 
    if(sheet.getRange(atIndex,6).getValue() ){
      return ContentService.createTextOutput( "已完成驗證過，請勿重複執行!");
    } 
    
    var now = new Date();
    sheet.getRange(atIndex,6).setValue(now);
    return ContentService.createTextOutput( "完成信件開啟之驗證");
  }
}

試算表內容:

Forms Service 可讓指令碼建立、存取及修改 Google 表單

開啟現存Google表單

開啟一個現有的 Google 表單，可以發現其實跟Google試算表、Google雲端硬碟等服務都一樣，幾乎都是使用 openById() 即可開啟。也可以使用 openByUrl() 喔!


var myForm = FormApp.openById('xxxxIdxxxx');

建立一個新的Google表單


var myForm = FormApp.create('表單名稱');

:pushpin: 無論是開啟現有或建立一個新的表單，最終都會回傳一個Form類別的物件回來，相關資料可參考: https://developers.google.com/apps-script/reference/forms/form

GAS 中 FormApp 常用的表單元件與函式對照

新增表單輸入項目:

addTextItem() 簡答(提供填寫單行文字)
addParagraphTextItem() 詳答(提供填寫多行文字)
addMultipleChoiceItem() 選擇題(圓形勾選鈕)
addCheckboxItem() 核取方塊(方形勾選鈕)
addListItem() 下拉式選單
addScaleItem() 線性刻度
addGridItem() 單選方格
addCheckboxGridItem() 核取方塊格
addDateItem() 日期(提供日期輸入)
addDurationItem() 時間(僅供時間輸入)
addDateTimeItem() 日期與時間

新增表單非輸入項目:

addImageItem() 新增圖片配置
addVideoItem() 影片項目

12/06學員提問

Google表單上傳圖片檔後，將圖片直接顯示在試算表中。



















function myFunction(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange( e.range.rowStart , e.range.columnEnd + 1 );
 
  var url = e.values[2];
  var fileId  = url.replace('https://drive.google.com/open?id=','');
  var file = DriveApp.getFileById(fileId);

  var bytes = file.getBlob().getBytes();
  var mimeType = file.getMimeType();
  var data = 'data:' + mimeType + ';base64,' + Utilities.base64Encode(bytes);
  
  var cellImage = SpreadsheetApp.newCellImage()
                .setSourceUrl(data)
                .build();
  range.setValue(cellImage);
  
}

記得要設定觸發條件！

試算表產生表單













































































function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('工作表1');
  var range = sheet.getRange(2,1,sheet.getLastRow()-1 ,sheet.getLastColumn());
  var data  = range.getValues();

  var form = FormApp.create('利用試算表建立的表單');
  form.setTitle('2023/12/08課堂實作')
    .setDescription("這是說明文字");
    
  data.forEach(function(item, index, array){
      var formItem;

      if(item[1] == '簡答'){
        formItem = form.addTextItem();
      }
      else if(item[1] == '詳答'){
        formItem = form.addParagraphTextItem();
      }
      else if(item[1] == '日期'){
        formItem = form.addDateItem();
      }
      else if(item[1] == '時間'){
        formItem = form.addTimeItem();
      }
      else if(item[1] == '日期時間'){
        formItem = form.addDateTimeItem();
      }
      else if(item[1] == '單選框'){
        formItem = form.addMultipleChoiceItem();
      }
      else if(item[1] == '複選框'){
        formItem = form.addCheckboxItem();
      }
      else if(item[1] == '下拉式選單'){
        formItem = form.addListItem();
      }
      else if(item[1] == '區段標題項目'){
        formItem = form.addSectionHeaderItem();
      }
      //設定問題的標題、說明文字
      formItem.setTitle(item[0]).setHelpText(item[2])

      //設定是否必填
      if( item[1] != '區段標題項目' ){
        formItem.setRequired(item[3]);
      }

      if( item[1] == '單選框' || item[1] == '複選框' || item[1] == '下拉式選單' ){
        //設定選項
         var choice = item[4].split(',');
         formItem.setChoiceValues(choice);
      }

      if(item[5] != '' && item[5] != '無' && item[1] == '簡答'){
        //設定輸入驗證
        var textValidation;

        if(item[5] == '數值'){
          textValidation = FormApp.createTextValidation().requireNumber().build();
        }
        else if(item[5] == '整數'){
          textValidation = FormApp.createTextValidation().requireWholeNumber().build();
        }
        else if(item[5] == '電子郵件'){
          textValidation = FormApp.createTextValidation().requireTextIsEmail().build();
        }
        else if(item[5] == '網址'){
          textValidation = FormApp.createTextValidation().requireTextIsUrl().build();
        }
        formItem.setValidation(textValidation);
      }
  });
  
  var editUrl = form.getEditUrl();
  Logger.log(editUrl)
}

範例成果：
https://docs.google.com/spreadsheets/d/17BQclwpConHBbqINuYc_EjmgDyUDs5-n6mKe8ItkMEo/edit?usp=sharing

五、Google 雲端硬碟

:pushpin: 有關透過Google Apps Script操作雲端硬碟之詳細使用方式及函式清單，可以參考以下網址：
Drive Sevice : https://developers.google.com/apps-script/reference/drive

範例程式 : 建立檔案



var fileName = "xxx.txt";
var content = "1234";
DriveApp.createFile(fileName,content);

小提示：把createFile()換成createFolder(name)，就是建立資料夾的方法

在指定資料夾建立檔案的方法






function myFunction() {
  var fileName = "xxx.txt";
  var content = "1234";
  var folder = DriveApp.getFolderById("輸入你的資料夾ID");
  folder.createFile(fileName,content);
}

DriveApp 常用函數

createFile() 建立檔案
createFolder() 建立資料夾
getFileById() 取得指定ID的檔案
getFilesByName() 取得指定名稱的檔案(可能取得多個檔案)
getFilesByType() 取得指定MIME Type的檔案(可能取得多個檔案)
getFiles() 取得所有檔案的集合
getFolderById() 取得指定ID資料夾
getFolders() 取得所有資料夾的集合
getFoldersByName(name) 取得指定名稱的資料夾(可能取得多個資料夾)
getRootFolder() 取得根目錄(最上層)的資料夾

取得檔案基本資料












function getFileInfo() {
  var file = DriveApp.getFileById("你的檔案ID");
  Logger.log(file.getName())
  Logger.log(file.getSize())
  Logger.log(file.getDescription())
  Logger.log(file.getUrl())
  Logger.log(file.getDownloadUrl())
  Logger.log(file.getId())
  Logger.log(file.getMimeType())
  Logger.log(file.getLastUpdated())
}

Class File 常用函數

makeCopy 複製檔案
moveTo 移動檔案
getId 取得檔案ID
getUrl 取得檔案URL(可用於在Google應用程式中開啟 File 的網址)
getDownloadUrl 取得檔案下載網址 *
getBlob 以blob形式取得檔案
getName 取得檔案名稱
getMimeType 取得檔案MIME Type
setTrashed 設定檔案是否在垃圾桶
setContent 設定檔案內容(或覆蓋)
setDescription 設定檔案說明內容
資料夾的操作方式類似，請參見Class Folder的介紹：
https://developers.google.com/apps-script/reference/drive/folder

從網路抓檔案 - UrlFetchApp

:pushpin: 有關透過Google Apps Script使用UrlFetchApp及函式清單，可以參考以下網址： https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app?hl=zh-tw

可以使用 UrlFetchApp 下載檔案，但是 UrlFetchApp 也有配額限制，且下載過程可能需要等待，不建議大量呼叫。


var f = UrlFetchApp.fetch("https://xx.xx/x.jpg").getBlob().setName('檔案名稱');

UrlFetchApp呼叫fetch()後可以取得回應內容(HTTPResponse)，以下為HTTPResponse物件幾個常見的函式：

getContentText() 取得文字資料
getBlob() 取得二進制大型物件
getContent() 取得原始二進制資料
getResponseCode() 取得HTTP Status Code
完整說明: https://developers.google.com/apps-script/reference/url-fetch/http-response?hl=zh-tw

多層次選單

https://docs.google.com/spreadsheets/d/12KTsygmRnsYDz1W15_ZHPAWbWHPmfFv4tEGwp8UqpJs/edit?usp=sharing

使用雲端硬碟產生下載網頁

新建獨立的GAS專案: https://script.new/

程式碼.gs























function doGet() {
  var output = HtmlService.createTemplateFromFile('Index');
  output.data = getFileList();
  return output.evaluate(); 
}

function getFileList() {
  var folder = DriveApp.getFolderById('此處改為你們的資料夾ID');
  var files = folder.getFiles();

  var fileList = [];
  while(files.hasNext()){
    var f = files.next();
    var item = [];
    item.push(f.getName());
    item.push(f.getDescription());
    item.push(f.getSize());
    item.push(f.getDownloadUrl());
    fileList.push(item);
  }
  return fileList;
}

Index.html































































































<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>檔案下載清單</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 0;
        }

        header {
            background-color: #333;
            color: #fff;
            padding: 1em;
            text-align: center;
        }

        table {
            width: 80%;
            margin: 20px auto;
            border-collapse: collapse;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            background-color: #fff;
        }

        th, td {
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }

        th {
            background-color: #333;
            color: #fff;
        }

        tr:hover {
            background-color: #f5f5f5;
        }

        .download-btn {
            display: inline-block;
            padding: 8px 16px;
            text-align: center;
            text-decoration: none;
            background-color: #4caf50;
            color: #fff;
            border-radius: 4px;
            transition: background-color 0.3s;
        }

        .download-btn:hover {
            background-color: #45a049;
        }
    </style>
  </head>
  <body>
      <header>
        <h1>檔案下載清單</h1>
    </header>

    <table>
        <thead>
            <tr>
                <th>檔案名稱</th>
                <th>檔案說明</th>
                <th>檔案大小</th>
                <th>下載</th>
            </tr>
        </thead>
        <tbody>
            <!-- 每一列代表一個檔案 --> 
              <?
                for(var i=0;i<data.length;i++) {
              ?>
              <tr>
                <td><?=data[i][0] ?></td>
                <td><?=data[i][1] ?></td>
                <td><?=data[i][2] ?></td>
                <td><a class="download-btn" href="<?=data[i][3] ?>" download>下載</a></td>               
              </tr>
              <?
                }
              ?>        
        </tbody>
    </table>
  </body>
</html>

六、Goolge 地圖

:pushpin: 有關同學提問地理位置的應用方法，實務上可結合Google地圖(Google Maps)來應用，可以參考以下網址：
Document Sevice : https://developers.google.com/apps-script/reference/maps?hl=zh-tw

這項服務可讓指令碼產生靜態地圖、規劃路線、將地址轉換為地理編碼座標，以及取樣高度。

取出EXIF，並將經緯度轉換為約略地址








function myFunction() {
   var file = Drive.Files.get('xxxxxxxxxxxxxxxxxx');
   var location = file.imageMediaMetadata.location;
   
   var info = Maps.newGeocoder().setLanguage("zh-TW").reverseGeocode(location.latitude,location.longitude);
   Logger.log(info.results[0]);
}

七、Google 文件

:pushpin: 有關透過Google Apps Script操作文件之詳細使用方式及函式清單，可以參考以下網址：
Document Sevice : https://developers.google.com/apps-script/reference/document

開啟及建立文件

開啟檔案的方法 (可用ID或URL開啟)


var doc1 = DocumentApp.openById('文件的ID');
var doc2 = DocumentApp.openByUrl('文件的URL');

建立檔案的方法


doc = DocumentApp.create('檔案名稱');

Google 文件的結構

參考資料：https://developers.google.com/apps-script/guides/docs
Document→Body→…
對於內容的操作，大多從getBody()開始

Class Document 常用函數

getBody 取得文件中的內文
getAs 取回指定類型的blob內容，僅支援轉換為PDF，請傳入「application/pdf」參數

Class Body 常用函數

範例程式:新增段落







var doc = DocumentApp.getActiveDocument();
var body = doc.getBody();

body.appendParagraph("A paragraph.");
body.appendPageBreak();

//參考來源: https://developers.google.com/apps-script/reference/document/body

範例程式：文字內容取代

基本的文字替換


var body = DocumentApp.getActiveDocument().getBody();
body.replaceText("{{name}}", "Tom");

帶有運算規則的作法


var body = DocumentApp.getActiveDocument().getBody();
body.replaceText("^.*Apps ?Script.*$", "Apps Script");

備註:這不是JavaScript的正規表達式，而是Google的RE2規則，若要進一步認識可參考 https://github.com/google/re2 / https://github.com/google/re2/wiki/Syntax / https://support.google.com/a/answer/1371417?hl=zh-Hant

範例程式、文字取代及PDF產生

:pushpin: 請先使用 Google文件來設計文件範本格式，並將要替換的部分使用 {{…}} 來標註(也可以用其他符號標註，重點是要使用程式碼來取代對應的標籤部分)，也可以使用 Microsoft Word 設計完後在上傳到 Google雲端硬碟，然後將其轉換為 Google文件的格式。


















function createDocumentForTemplater() {
  var templater_id = "範本檔案的ID";
  var templater_file = DriveApp.getFileById(templater_id).makeCopy();

  var doc = DocumentApp.openById(templater_file.getId());
  var body = doc.getBody();
  body.replaceText("{{name}}" , "王曉明");
  body.replaceText("{{id}}", "12345678");
  doc.saveAndClose();

  var folder_id = "目標存放資料夾的ID";
  var folder = DriveApp.getFolderById(folder_id);
  templater_file.moveTo(folder).setName("20231220");

  var pdf = templater_file.getAs('application/pdf');
  folder.createFile(pdf).setName("20231220001.pdf");
}

2023/12/20 課程實作：從表單，產生PDF到信件 (要附加在Google表單所屬的試算表上，並設定觸發條件→ 提交表單時)
















function myFunction(e) {
  var folder = DriveApp.getFolderById('資料夾ID');
  var file = DriveApp.getFileById('文件範本ID').makeCopy('20231220',folder)
  var doc = DocumentApp.openById(file.getId());
  var body = doc.getBody();
  body.replaceText("{{姓名}}", e.values[2] );
  body.replaceText("{{課程名稱}}", e.values[3] );
  doc.saveAndClose();
  var pdf = doc.getAs('application/pdf');
  folder.createFile(pdf).setName('20231220.pdf');

  var subject = "自動寄發獎狀通知信";
  var message = "您好，這裡是信件內容";
  var email = e.values[1];
  MailApp.sendEmail(email, subject, message, { attachments: [pdf] });
}

Google官方範例 - 電子郵件合併解決方案

https://developers.google.com/apps-script/samples/automations/mail-merge?hl=zh-tw

八、Google Workspace 自訂選單

:pushpin: 參考資料 : https://developers.google.com/apps-script/guides/menus

可以透過指令碼擴充 Google Workspace 應用程式(例如文件、試算表)的功能選單。若希望在使用者開啟檔案時就在工具列上顯示自訂選單，可在 onOpen() 函式中撰寫擴充自訂選單的程式碼。

UI類別的介紹：https://developers.google.com/apps-script/reference/base/ui?hl=zh-tw
顏色代碼： https://htmlcolorcodes.com/

自訂選單 - 以試算表為例

步驟:

先用 SpreadsheetApp.getUi() 取得UI物件
接下來，使用UI物件的 createMenu() 函式建立選單
建立後，使用各項函式建立選項內容
最後，使用 addToUi(); 將選單增加到編輯器的UI上

建立選單常用函式

var ui = SpreadsheetApp.getUi(); - 取得UI物件
var menu = ui.createMenu('自訂選單'); - 新增自訂選單
menu.addItem('選項', 'fun1'); - 新增選項 (參數1:選項文字，參數2:點選後所執行的function)
menu.addSeparator(); - 新增分隔
menu.addToUi(); - 將選單增加到編輯器使用者介面中














































function myFunction(){
    var ui = SpreadsheetApp.getUi();
    //建立一個新的自訂選單，變數名稱為menu
    var menu = ui.createMenu('自訂選單');
    //新增選項
    menu.addItem('設定醒目標示','myFunction2')
    //新增分隔線
    menu.addSeparator();
    //新增子選單
    var menu2 = ui.createMenu('子選單');
    menu2.addItem('寄信', 'sendMail');
    //將子選單增加到menu內
    menu.addSubMenu(menu2);
    //將選單(menu)新增到UI最上層選單(工具列)
    menu.addToUi();
}

function sendMail(){
  var spreadsheet = SpreadsheetApp.getActive();
  if( spreadsheet.getCurrentCell().offset(0, 3).getValue() == '' ){
    var subject = spreadsheet.getCurrentCell().offset(0, 1).getValue();
    var message = spreadsheet.getCurrentCell().offset(0, 2).getValue();
    var email = spreadsheet.getCurrentCell().getValue();
    MailApp.sendEmail (email, subject, message);

    var time = new Date();
    var now = time.toLocaleString();
    spreadsheet.getCurrentCell().offset(0, 3).setValue(now);
  }
}



function myFunction2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
  spreadsheet.getCurrentCell().offset(-1, 0).activate();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
  spreadsheet.getCurrentCell().offset(1, 1).activate();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
  spreadsheet.getCurrentCell().offset(0, -2).activate();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
  spreadsheet.getCurrentCell().offset(1, 1).activate();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
};

其他UI常用函式

https://developers.google.com/apps-script/guides/dialogs?hl=zh-tw

alert : 對話方塊
prompt : 對話方塊，並提供使用者輸入文字回應
showModalDialog : 建立更複雜的對話方塊，支援使用HTML顯示對話方塊內容

建立 Google Workspace - appsscript.json


















{
  "timeZone": "Asia/Taipei",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "addOns" : {
      "common": {
        "name" : "自訂選單小工具",
        "logoUrl" : "https://cee.ksu.edu.tw/images/ksu-logo.png"
      },
      "sheets" : {
        "homepageTrigger" : {
          "runFunction" : "onOpen"
      } 
    }
  }
}

AddOns 資訊清單資源: https://developers.google.com/apps-script/manifest/addons?hl=zh-tw#common



































































function onOpen(e){
    var ui = SpreadsheetApp.getUi();
    //建立一個新的自訂選單，變數名稱為menu
    var menu = ui.createMenu('自訂選單');
    //新增選項
    menu.addItem('設定醒目標示','myFunction2')
    menu.addItem('顯示快顯視窗','showModal')
    
    //新增分隔線
    menu.addSeparator();
    //新增子選單
    var menu2 = ui.createMenu('子選單');
    menu2.addItem('寄信', 'sendMail');
    //將子選單增加到menu內
    menu.addSubMenu(menu2);
    //將選單(menu)新增到UI最上層選單(工具列)
    menu.addToUi();
}

function sendMail(){
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert('信件通知','請問您是否確認要寄發通知信件？',ui.ButtonSet.YES_NO); 
  if(response == ui.Button.YES){

    var input = ui.prompt('請輸入信件結尾要出現的文字');
    
    var spreadsheet = SpreadsheetApp.getActive();
    if( spreadsheet.getCurrentCell().offset(0, 3).getValue() == '' ){
      var subject = spreadsheet.getCurrentCell().offset(0, 1).getValue();
      var message = spreadsheet.getCurrentCell().offset(0, 2).getValue() + '' + input.getResponseText()  ;
      var email = spreadsheet.getCurrentCell().getValue();
      MailApp.sendEmail (email, subject, message);

      var time = new Date();
      var now = time.toLocaleString();
      spreadsheet.getCurrentCell().offset(0, 3).setValue(now);
      ui.alert('執行結果','寄信成功' , ui.ButtonSet.OK)
    }
    else{
        ui.alert('執行結果','此筆紀錄已曾經寄發通知信過，不須再次寄發。' , ui.ButtonSet.OK)
    }
  }
}

function showModal(){
  var htmlOutput = HtmlService
    .createHtmlOutput('<p>A change of speed, a change of style...</p>')
    .setWidth(250)
    .setHeight(300);
  var ui = SpreadsheetApp.getUi();
  ui.showModelessDialog(htmlOutput, 'My add-on');
}

function myFunction2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
  spreadsheet.getCurrentCell().offset(-1, 0).activate();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
  spreadsheet.getCurrentCell().offset(1, 1).activate();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
  spreadsheet.getCurrentCell().offset(0, -2).activate();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
  spreadsheet.getCurrentCell().offset(1, 1).activate();
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
};

九、LINE Notify 整合

:pushpin: 如果需要 LINE Notify 更完整的功能整合，請參考 LINE Notify API 官方文件 : https://notify-bot.line.me/doc/

LINE Notify是LINE所提供的一項服務，請先開啟以下 LINE Notify 網站網址，並加入 LINE Notify 的好友 https://notify-bot.line.me/zh_TW/

LINE Notify的 LINE ID: @linenotify

LINE Notify 可以個別一對一傳送訊息給目標，也可以將訊息傳送到特定群組上

將 LINE Notify 帳號加入好友清單後，請開啟 https://notify-bot.line.me/zh_TW/ 網站並從右上角的「登入」按鈕開啟登入頁面，並登入您的帳號
登入後，從右上角的選單開啟「個人頁面」
進入後，在==「發行存取權杖（開發人員用）」點選「發行權杖」==
選擇「群組」或「1對1模式」

5.取得 LINE Notify 權杖，請務必先複製再關閉喔!

6.發行結果如下，如要取消可點選「解除」

權杖發行或解除後，也會透過LINE訊息通知喔!

LINE Notify 的限制

單一個人申請的發行權杖每小時最多能發送1000則訊息
每則訊息不可超過1000個字
每個基本使用者最多可以申請100個權杖
圖片大小也有限制，更詳細的限制可以參考API文件

最基本的 LINE Notify 訊息發送程式範例













function sendLineNotifyMessage() {
  var token = '請在這裡貼入 LINE Notify 發行權杖'; 
  var options =
   {
     "method"  : "POST",
     "headers" : {"Authorization" : "Bearer "+ token},
     "payload":{
        'message': 'Hello World!' 
     }
   };
   UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
}

:pushpin: UrlFetchApp可以用來擷取、連結外部服務或資源，讓 Google Apps Script 程式碼可以與其他應用程式通訊(發出HTTP請求)，詳細參考文件 : https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app

程式碼執行後的通知訊息發送結果：
請注意，發送的訊息前方會加上【…】來標示建立時所設定的權杖名稱，

透過 LINE Notify 訊息發送圖片

LINE Notify 也可以發送圖片，可採用以下2種方式，將參數添加到原本的JSON結構上傳遞:

加上imageFullsize (完整圖片，最大尺寸為 2048×2048px JPEG) 及imageThumbnail (縮圖，最大尺寸為 240×240px JPEG)，分別傳入URL即可。
加上imageFile，傳入照片檔案的二進制格式，支援JPEG及PNG格式；這個動作會將圖片上傳到LINE伺服器上。(具有每小時上傳次數的限制)

備註：如果imageFullsize、imageThumbnail、imageFile三個屬性都一起傳送，則會LINE Notify優先使用imageFile。

附帶一提，LINE Notify也可以傳遞貼圖，只要照API文件內的說明傳入stickerPackageId及stickerId參數即可。

將雲端硬碟上的圖片上傳至 LINE Notify
















function sendLineNotifyMessage() {
  var token = '你的權杖'; 
  var imageFile = DriveApp.getFileById("你雲端硬碟檔案上的ID").getBlob();


  var options =
   {
     "method"  : "POST",
     "headers" : {"Authorization" : "Bearer "+ token},
     "payload":{
        'message': 'GAS訊息測試_只傳圖片' ,      
        'imageFile' : imageFile,
     }
   };
   UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
}

LINE Notify 傳遞貼圖















function sendLineNotifyMessage() {
  var token = '你的權杖'; 

  var options =
   {
     "method"  : "POST",
     "headers" : {"Authorization" : "Bearer "+ token},
     "payload":{
        'message': 'GAS訊息測試_只傳圖片' ,    
        'stickerPackageId' : '789' ,
        'stickerId' : '10855', 
     }
   };
   UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
}

貼圖ID：https://developers.line.biz/en/docs/messaging-api/sticker-list/#send-sticker

設計Goolge Form接收文字及圖片後，使用LINE Notify送出訊息




















function myFunction(e) {
  var message = '姓名：' + e.values[1] + '\n電話：' + e.values[2] + '\n填表時間：' + e.values[0];  
  var fileId  = e.values[3].replace('https://drive.google.com/open?id=','');   
  sendLineNotifyImage(message,fileId);
}

function sendLineNotifyImage(message,fileId) {
  var token = '要換成你們的Token!!!!!'; 
  var imageFile = DriveApp.getFileById(fileId).getBlob();
  var options =
   {
     "method"  : "POST",
     "headers" : {"Authorization" : "Bearer "+ token},
     "payload":{
        'message': message ,      
        'imageFile' : imageFile,
     }
   };
   UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
}

記得要設定觸發條件(當表單提交時)

LINE Notify 登錄服務

前面已經學到如何發送 LINE Nofity 訊息的基本技能了，但卻只能發送給開發者自己的帳號或指定的聊天室。
如果需要發送給不特定人，就需要完整的整合登入機制，首先要先將你的服務登錄到 LINE Notify 後台，並依據其所提供的 OAuth 機制設計一個網頁介面提供登入認證，登入後取得權杖後即可發送訊息給特定LINE帳號。

1. 基本流程概觀：

呼叫 https://notify-bot.line.me/oauth/authorize ，取得使用者的授權碼。如果使用者沒有登入LINE，則會跳到LINE的登入介面。
呼叫 https://notify-bot.line.me/oauth/token ，取得對應的權杖(Token)，此權杖就是日後要發送訊息給這位使用者的依據，所以程式後端應該將這個權杖儲存起來，以供日後訊息發送之用。
接下來，就可以使用上述取得的權杖進行訊息發送。

2. 連結功能的具體開發流程：

步驟一、到 LINE Nofity 網頁完成服務登錄並取得相關資訊
請前往　:　https://notify-bot.line.me/my/services/

步驟三、建立提供使用者登錄的網頁介面

重要小提示：重要且不對外開放呼叫的函式應該設為私有函式，也就是要在函式名稱後加上下底線(_)
index.html

































<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <base target="_top">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-rbsA2VBKQhggwzxH7pPCaAqO46MgnOM80zW1RWuH61DGLwZJEdK2Kadq2F9CUG65" crossorigin="anonymous">
    <script>
        function linkLineNotify() {
            var URL = 'https://notify-bot.line.me/oauth/authorize?';
            URL += 'response_type=code';
            URL += '&client_id=<?=client_id ?>';
            URL += '&redirect_uri=<?=redirect_uri ?>';
            URL += '&scope=notify&state=NO_STATE';
            window.open(URL, "帳號驗證", "top");

        }
    </script>
    <title>LINE Notify 測試</title>
  </head>
  <body>
    <div class="container">
      <div class="row">
        <div class="col-12"> 
          <div class="d-grid">
            <button onclick="linkLineNotify();" class="btn btn-primary"> 連結 LINE Notify 通知服務 </button>      
          </div>
        </div>
      </div>
    </div>
  </body>
</html>

步驟三、接收Callback處理函式(用GET接收)
程式碼.gs
























































var redirect_uri = "URL";
var client_id = "你的client_id";
var client_secret = "你的client_secret";

function doGet(e){
  if(e.parameter.code){
    var data = JSON.parse(getAccessToken_(e.parameter.code));
    var str = "連結成功！你的權杖是: " + data.access_token;
    SpreadsheetApp.getActiveSheet().appendRow([data.access_token]);
    return HtmlService.createHtmlOutput(str);
  }
  else{
   return HtmlService.createTemplateFromFile("index").evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1');
  }
}

function sendMessageToAll(){
  var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  for(var i = 0; i < data.length ; i++ ){   
      try{
        sendLineNotifyMessage_("Hello" , data[i][0])
      }
      catch{

      }
  }
}

function getAccessToken_(code){  
  var options =
   {
     "method"  : "post",
     "Content-Type" : "application/x-www-form-urlencoded",
     "payload" : {
        "grant_type" : "authorization_code",
        "code" : code,
        "redirect_uri": redirect_uri,
        "client_id" : client_id,
        "client_secret" : client_secret
      }
   };
   var response = UrlFetchApp.fetch("https://notify-bot.line.me/oauth/token", options); 
   return response;
}

function sendLineNotifyMessage_(str,token) {
  var options =
   {
     "method"  : "post",
     "headers" : {"Authorization" : "Bearer "+ token},
     "payload":{
        'message': str 
     }
   };
   var response = UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options); 
}

小提示：加上 .addMetaTag('viewport', 'width=device-width, initial-scale=1') 是因為 Google Apps Script的網頁被放置在iframe中，為了達到RWD的效果，必須在外層網頁加上這段Meta Tag。

送出後的完成介面
ok.html





































































<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
      <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>帳號連動成功</title>
    <style>
        body {
            font-family: 'Arial', sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 0;
            display: flex;
            align-items: center;
            justify-content: center;
            height: 100vh;
        }

        .container {
            background-color: #fff;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            overflow: hidden;
            width: 80%;
            max-width: 400px;
            text-align: center;
            padding: 20px;
            box-sizing: border-box;
        }

        .success-message {
            color: #4CAF50;
            font-size: 24px;
            font-weight: bold;
            margin-bottom: 20px;
        }

        .success-icon {
            color: #4CAF50;
            font-size: 50px;
            margin-bottom: 20px;
        }

        .back-to-home {
            background-color: #4CAF50;
            color: #fff;
            padding: 10px 20px;
            text-decoration: none;
            border-radius: 5px;
            display: inline-block;
            transition: background-color 0.3s ease;
        }

        .back-to-home:hover {
            background-color: #45a049;
        }
    </style>
  </head>
  <body>
      <div class="container">
        <div class="success-icon">&#10004;</div>
        <div class="success-message">帳號連動成功！</div>
        <p>您的帳號已成功連動。感謝您的使用。</p>
        <a href="#" class="back-to-home">返回首頁</a>
    </div>
  </body>
</html>

網頁顯示結果:

點選連結按鈕並登入後:

成果體驗：
https://script.google.com/a/g.ksu.edu.tw/macros/s/AKfycbxXMTP6bWgRb6t4VOqPOm1nQq-mZ-S4SVsmwDUofb_vFEq-_1KAeGPK80JJAhKsF_ig-g/exec

十、串接Google Cloud Platform(GCP) BigQuery

Google Cloud Platform（GCP）是由Google提供的一個雲端計算平台，提供可擴展、靈活且高效的雲端服務。GCP 提供了一系列基礎設施、平台和服務，可用於構建、部署和擴展應用程序和服務。

問ChatGPT，何謂BigQuery：

Google BigQuery 是一種大數據分析服務，由Google Cloud提供。它是一個完全托管、高度擴展且高性能的分析型資料倉儲，旨在處理大規模數據集，並支援快速 SQL 查詢。

以下是 Google BigQuery 的一些主要特點和功能：

完全托管服務： BigQuery 是一個完全托管的雲端服務，無需擔心基礎設施的管理。Google Cloud負責所有硬體和軟體的管理，讓用戶能夠專注於數據分析。

高度擴展性： BigQuery 適用於處理大規模數據集，能夠擴展到數百PB的數據。這種高度擴展性使其成為處理龐大數據的理想解決方案。

快速 SQL 查詢：使用標準的SQL語法，用戶可以輕鬆地查詢大數據集，而無需學習新的查詢語言。BigQuery的查詢速度非常快，可在秒內處理數據。

支援即席查詢： BigQuery 支援即席查詢，用戶可以在不事先定義模式的情況下直接查詢數據。這使得用戶能夠靈活地對數據進行探索和分析。

整合性： BigQuery 與其他Google Cloud服務和工具無縫整合，包括Google Cloud Storage、Cloud Dataflow、Cloud Dataprep等。同時，它也支援第三方工具和BI平台的整合。

多層次的安全性： BigQuery 提供多層次的安全性機制，包括數據加密、身份驗證、訪問控制等，以確保數據的安全性和隱私。

計費模型： BigQuery 的計費是基於查詢的數據掃描量，而不是預先分配的固定容量。這種計費模型讓用戶只需支付實際使用的資源，提高了成本效益。

問ChatGPT，何謂SQL語法：

SQL（Structured Query Language）是一種用於管理和操作關聯式資料庫的標準化語言。它提供了一套用於定義、查詢、更新和管理資料庫的指令和語法。SQL 被廣泛應用於各種資料庫管理系統（DBMS），包括MySQL、Microsoft SQL Server、PostgreSQL、Oracle Database等。

以下是 SQL 語法中的一些基本元素和常見的操作：

SQL（結構化查詢語言）是一種標準化的語言，用來管理和操作資料庫管理系統（DBMS）中的資料庫。以下是一些基本的 SQL 語法：

查詢資料：

SELECT 欄位1, 欄位2 FROM 資料表 WHERE 條件;

這個語句用來從指定的資料表中挑選特定欄位的資料，可以使用 WHERE 子句進行條件過濾。

插入資料：

INSERT INTO 資料表 (欄位1, 欄位2) VALUES (值1, 值2);

這個語句用來向資料表中新增資料列。

更新資料：

UPDATE 資料表 SET 欄位1 = 值1 WHERE 條件;

這個語句用來更新資料表中已存在資料的值，可以使用 WHERE 子句進行條件過濾。

刪除資料：

DELETE FROM 資料表 WHERE 條件;

這個語句用來從資料表中刪除符合指定條件的資料列。

建立資料表：

CREATE TABLE 資料表名稱 (
    欄位1 資料類型,
    欄位2 資料類型,
    ...
);

這個語句用來建立新的資料表，並指定各欄位的資料類型。

查詢資料表結構：

DESC 資料表名稱;

這個語句用來查詢指定資料表的結構，即資料表的欄位和資料類型。

SQL學習資源： https://www.1keydata.com/tw/sql/sql.html

GAS 整合 BigQuery 的需求條件

後續GAS指令碼專案中要啟用「進階服務」
GAS指令碼使用的 Cloud Platform (GCP) 專案中啟用與進階服務對應的 API。

在 GAS 中加入 BigQuery API 函式庫

在 Google Apps Script 編輯器中，點擊左側的「服務 + 」，在「進階 Google 服務」對話框中找到「BigQuery API」並啟用它。

新增成功後畫面應該長得像下面這樣(服務中多一個BigQuery)

可參考BigQuery服務的說明文件：https://developers.google.com/apps-script/advanced/bigquery?authuser=0&hl=zh-tw

在 GCP 中啟用 BigQuery 服務

開啟 Google Cloud 首頁，並進入控制台(右上有按鈕)。
新增專案
選擇「在Big Query中執行查詢」按鈕
如果是第一次進入，可能會看到以下頁面，點選完成即可
進入後，看到畫面如下

https://developers.google.com/apps-script/guides/cloud-platform-projects?authuser=0&hl=zh-tw

建立資料表(基礎)

首先，要先建立「資料集」 (DataSet)
輸入資料集名稱以建立
接下來，在所建立的資料集上建立「資料表」(Table)
輸入資料表名稱及相關設定來建立
建立成功後如下圖所示
接下來，點選資料表，並點選「編輯結構定義」來加入資料欄位
設定各項資料欄位

在GAS使用SQL語法查詢資料

































function queryBigQueryTable() {
  // 設定 BigQuery 資訊
  var projectId = 'plasma-air-409816';
  var datasetId = 'myDataSet';
  var tableId = 'myTable';

  // 構建 SQL 查詢
  var sqlQuery = 'SELECT * FROM `'+projectId+'.'+datasetId+'.'+tableId+'`';

  // 執行 BigQuery 查詢
  var queryResults = BigQuery.Jobs.query({
    query: sqlQuery,
    useLegacySql: false
  }, projectId);

  // 等待查詢完成
  while (!queryResults.jobComplete) {
    Utilities.sleep(1000);
    queryResults = BigQuery.Jobs.getQueryResults(projectId, queryResults.jobReference.jobId);
  }
  
  // 獲取查詢結果
  if(queryResults.rows){
    var rows = queryResults.rows;
    for (var i = 0; i < rows.length; i++) {
      var row = rows[i];
      Logger.log(row);
    }
  }
  else{
    Logger.log('查無資料');
  }
}

若是使用DML (如insert、update、delete)，若未啟用帳單帳戶，則會出現以下訊息

修改為參數化查詢的方案篩選資料

若要在SQL語法篩選資料，可利用WHERE關鍵字來加入篩選條件，但要注意 SQL injection 的資安疑慮，為了避免這項問題，一般會採用「參數化查詢」(parameterized query)的方式來處理

只將中間的程式碼片段替換如下


















  // 構建 SQL 查詢
  var name = 'John';
  var sqlQuery = 'SELECT * FROM `'+projectId+'.'+datasetId+'.'+tableId+'` WHERE `name` = @name';

  // 執行 BigQuery 查詢
  var queryResults = BigQuery.Jobs.query({
    query: sqlQuery,
    useLegacySql: false,
    queryParameters: [
        {
          name : 'name',
          parameterType: {type: 'STRING'},
          parameterValue: {value: name}
        }
      ]
  }, projectId);

在GAS使用JSON格式寫入資料




































function insertDataToBigQueryTable() {
  // 設定 BigQuery 資訊
  var projectId = 'plasma-air-409816';
  var datasetId = 'myDataSet';
  var tableId = 'myTable';

  // 構建插入資料的 JSON 物件
  var data = {
    rows: [
      { insertId: '1', json: { "name": "John", "address": "123 Main St", "email": "john@example.com", "mobile": "123-456-7890" } },
      { insertId: '2', json: { "name": "Jane", "address": "456 Oak St", "email": "jane@example.com", "mobile": "987-654-3210"  } },
      // 增加更多範例資料...
    ]
  };

  try {
    // 執行 BigQuery 插入資料
    var insertResults = BigQuery.Tabledata.insertAll(
      data,
      projectId,
      datasetId,
      tableId
    );

    // 檢查是否有插入錯誤
    if (insertResults.insertErrors && insertResults.insertErrors.length > 0) {
      Logger.log('寫入資料發生錯誤: ' + JSON.stringify(insertResults));
    } else {
      Logger.log('資料寫入成功');
    }
  } catch (error) {
    Logger.log('寫入資料發生錯誤: ' + error.message);
  }
}

小提示：可考慮用 Utilities.getUuid() 產生唯一值

免費帳戶會出現以下訊息

也可以串接 GCP Cloud SQL

在GCP上可以建立資料庫，但這是要錢的…

Google Apps Script 的 JDBC 類別 : https://developers.google.com/apps-script/guides/jdbc?hl=zh-tw

十一、可重複使用的程式碼

設計程式庫

https://developers.google.com/apps-script/guides/libraries?hl=zh-tw#using_a_library

以下是編寫程式庫時應遵循的準則：(來自Google文件)

請為專案選擇有意義的名稱，因為其他人在包含您的程式庫時，這個 ID 會用來當做預設 ID。
如果您不希望程式庫使用者看到 (或無法使用) 的指令碼方法，您可以在方法名稱結尾加上底線。例如 myPrivateMethod_()。
程式庫使用者只會看到可列舉的全域屬性。這包括函式宣告、在函式外使用 var 建立的變數，以及在全域物件上明確設定的屬性。舉例來說，將 enumerable 設為 false 的 Object.defineProperty() 會建立可在程式庫中使用的符號，但使用者無法存取這個符號。
如果希望程式庫使用者使用指令碼編輯器的自動完成功能和自動產生的說明文件，則所有函式都必須提供 JSDoc 樣式說明文件。範例如下：








/**
 * Raises a number to the given power, and returns the result.
 *
 * @param {number} base the number we're raising to a power
 * @param {number} exp the exponent we're raising the base to
 * @return {number} the result of the exponential calculation
 */
function power(base, exp) { ... }

部署程式庫

當完成程式庫開發後，接下來就是要進行部署

1. 將GAS專案部署為「程式庫」

:pushpin: 在GAS開發環境的清單中，中文是顯示「資料庫」，但點選後是顯示「程式庫」，這邊的中文翻譯很亂，我認為「程式庫」或「函式庫」是較好的稱呼，本文採用「程式庫」稱之。

選單將程式碼建構成「資料庫」類型(Libraries)，將可分享給其他專案使用

預設只有自己帳號的專案可以使用，如果想要分享給其他人使用，還需要將專案設定共用。

2. 在其他專案中引用現有「程式庫」程式功能

點選資料庫旁邊的增加按鈕

填入完整的程式庫指令碼，並點選「查詢」

若內容無誤，則可點選「新增」加入，成功後應顯示如下圖

練習用指令碼ID : 1eNkZL3BfQ7bZk3mBgdxdjQ9YEJ_mk6GQxWbJJQsRkImfRT6lGHq4iPzJ

利用GAS取代文字及圖片:
Demo URL : https://docs.google.com/forms/d/e/1FAIpQLSdfpL9WkrNhJlYhrQvQ9Ur0lzB72KN7cWZ_i4HumL1HZEwt9A/viewform






























function myFunction(e) {
  var file = DriveApp.getFileById('1HAuLVv0SyuqWkFbig9_XWbDypWPy86ZxY686CkiyZEU');
  var fileCopy = file.makeCopy();
  var doc = DocumentApp.openById(fileCopy.getId());

  var body = doc.getBody();
  body.replaceText("{{name}}" , e.values[1]);
  body.replaceText("{{id}}", e.values[2]);
  body.replaceText("{{unit}}", e.values[3]);

  var photofileId  = e.values[4].replace('https://drive.google.com/open?id=','');
  var photoFile =  DriveApp.getFileById(photofileId);
  var photoArea = body.findText("{{photo}}").getElement().getParent();
  photoArea.clear();
  var photo = photoArea.asParagraph().appendInlineImage(photoFile.getBlob());
  photo.setWidth(200);
  photo.setHeight(350);
  doc.saveAndClose();
  
  var fileName = e.values[2] + '_' + e.values[1] + '.pdf';
  var pdf = doc.getAs('application/pdf').setName(fileName);

  var subject = "提供個人資料表";
  var message = "您好，這裡是信件內容";
  var email = e.values[5];
  MailApp.sendEmail(email, subject, message, { attachments: [pdf] });

}

網路上好用的現有程式庫

Google Apps Script Libraries

TemplateApp
GitHub: https://github.com/tanaikech/TemplateApp
GAS: https://script.google.com/u/0/home/projects/1DbOiAxK9Kwndl-5848xLP91xhUzTidfXkBOxBpWG2uw8fxdvf0Vt3sk4/edit























function sample1() {
  const templateId = "1HAuLVv0SyuqWkFbig9_XWbDypWPy86ZxY686CkiyZEU"; // Please set your template ID. In this case, please set the Google Document ID as the template.
  const folder_ = DriveApp.getFolderById("1Fm6v5XbWlUVJwhOURtxiZ1dYrkObpbcZ"); // Please set your folder ID. The result Document is put into the folder.

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName("工作表1"); // Please set your sheet name.
  const range = sheet.getDataRange();

  const n = range.getNumRows() - 1; // Number of templates.
  const file = DriveApp.getFileById(templateId);
  const documents = [...Array(n)].map((_, i) =>
    DocumentApp.openById(file.makeCopy(`sample_doc_${i + 1}`, folder_).getId())
  );

  const object = {
    range,
    document: { documents },
    useStyleOfSpreadsheet: true,
    //useImageAsPlaceholder: true,
    // excludeTextStyles: ["fontFamily", "fontSize"],
  };
  TemplateApp.sheetRangeToDocuments(object, console.log);
}

DocsServiceApp
GitHub: https://github.com/tanaikech/DocsServiceApp
GAS: https://script.google.com/home/projects/108j6x_ZX544wEhGkgddFYM6Ie09edDqXaFwnW3RVFQCLHw_mEueqUHTW/edit
ImaApp
GitHub: https://github.com/tanaikech/ImgApp
GAS: https://script.google.com/home/projects/1T03nYHRho6XMWYcaumClcWr6ble65mAT8OLJqRFJ5lukPVogAN2NDl-y/edit
ZipFolder
GitHub: https://github.com/tanaikech/ZipFolder
GAS: https://script.google.com/home/projects/1q5FY5UxNpFNYxtd-LZgIjchicKq1BfDCVPbOwA0BDiL6zfCTxjfe-Puz/edit
HtmlFormApp :
GitHub: https://github.com/tanaikech/HtmlFormApp
GAS: https://script.google.com/macros/d/1uLJrVXGaI-ceHFl_VC1U5jcynKpR2qnNG2tNPd03QJZw1jCcKw2_Oiwh/edit /
範例：https://medium.com/google-cloud/easily-implementing-html-form-with-google-spreadsheet-as-database-using-google-apps-script-66472ab7bf6c
PDFApp
GitHub: https://github.com/tanaikech/PDFApp
GAS: https://script.google.com/home/projects/1Xmtr5XXEakVql7N6FqwdCNdpdijsJOxgqH173JSB0UOwdb0GJYJbnJLk/edit
Replacing Text to Image for Google Document using Google Apps Script
https://gist.github.com/tanaikech/f84831455dea5c394e48caaee0058b26

補充: 外掛程式 (實現合併列印功能)

autoCrat
https://workspace.google.com/marketplace/app/autocrat/539341275670

補充：JavaScript的日期相關處理函式

MDN : https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date

取得當下時間的方法

Date.now() ：　取得自 1970 年 1 月 1 日 00:00:00 UTC 以來經過的毫秒數
new Date() ：若不傳入值，則提供目前時間的字串，參考資料: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/Date

格式轉換

參考資源:















Date.prototype.format = function (fmt) {
  var o = {
    "M+": this.getMonth() + 1, //月份
    "d+": this.getDate(), //日
    "h+": this.getHours(), //小時
    "m+": this.getMinutes(), //分
    "s+": this.getSeconds(), //秒
    "q+": Math.floor((this.getMonth() + 3) / 3), //季度
    "S": this.getMilliseconds() //毫秒
  };
  if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
  for (var k in o)
  if (new RegExp("(" +  k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" +  o[k]).substr(("" + o[k]).length)));
  return fmt;
}

範例：顯示當下日期並格式化輸出


var str = (new Date()).Format("yyyy-MM-dd hh:mm:ss");
console.log(str);

圖片寬度自動適應











  var photoFile =  DriveApp.getFileById('圖片FILE ID');
  var photoArea = body.findText("{{photo}}").getElement().getParent();
  photoArea.clear();
  var photo = photoArea.asParagraph().appendInlineImage(photoFile.getBlob());
//-----
  var width = 300;
  var w = photo.getWidth();
  var h = photo.getHeight();
  photo.setWidth(width);
  photo.setHeight(width * h / w);
//----

新聞分享：Google整合BigQuery與Document AI簡化文件資料擷取工作 (ITHome 2024-01-08發表)

請參閱：https://www.ithome.com.tw/news/160695

112年 - Google Apps Script雲端程式設計實務班

tags: Google Apps Script 產業人才投資方案 授課資料

:memo: 課前準備

Step 1: 上課前應準備的

Step 2: 課前能力檢核

一、Google 試算表

Google Sheet 的 GAS API

名稱對照

GAS開啟試算表

如何在試算表使用Google翻譯?

Google試算表自訂函數

二、Google Apps Script 與網頁應用程式

如何從GAS顯示資料為HTML?

練習一：將變數傳遞到HTML端

練習二：結合試算表資料及迴圈呈現結果

設計具有表單互動的GAS的網頁應用程式

資料查詢的程式範例

其他常見方法：

三、Gmail 電子郵件的應用

起手式：簡單的電子郵件發送功能

發送具有附件的電子郵件

四、Google 表單

開啟現存Google表單

建立一個新的Google表單

GAS 中 FormApp 常用的表單元件與函式對照

12/06學員提問

試算表產生表單

五、Google 雲端硬碟

範例程式 : 建立檔案

在指定資料夾建立檔案的方法

DriveApp 常用函數

取得檔案基本資料

Class File 常用函數

從網路抓檔案 - UrlFetchApp

多層次選單

使用雲端硬碟產生下載網頁

六、Goolge 地圖

七、Google 文件

開啟及建立文件

Google 文件的結構

Class Document 常用函數

Class Body 常用函數

範例程式:新增段落

範例程式：文字內容取代

範例程式、文字取代及PDF產生

Google官方範例 - 電子郵件合併解決方案

八、Google Workspace 自訂選單

自訂選單 - 以試算表為例

建立選單常用函式

其他UI常用函式

建立 Google Workspace - appsscript.json

九、LINE Notify 整合

LINE Notify 的限制

最基本的 LINE Notify 訊息發送程式範例

透過 LINE Notify 訊息發送圖片

將雲端硬碟上的圖片上傳至 LINE Notify

LINE Notify 傳遞貼圖

設計Goolge Form接收文字及圖片後，使用LINE Notify送出訊息

LINE Notify 登錄服務

1. 基本流程概觀：

2. 連結功能的具體開發流程：

十、串接Google Cloud Platform(GCP) BigQuery

問ChatGPT，何謂BigQuery：

問ChatGPT，何謂SQL語法：

GAS 整合 BigQuery 的需求條件

在 GAS 中加入 BigQuery API 函式庫

在 GCP 中啟用 BigQuery 服務

建立資料表(基礎)

在GAS使用SQL語法查詢資料

修改為參數化查詢的方案篩選資料

在GAS使用JSON格式寫入資料

也可以串接 GCP Cloud SQL

十一、可重複使用的程式碼

設計程式庫

部署程式庫

1. 將GAS專案部署為「程式庫」

2. 在其他專案中引用現有「程式庫」程式功能

網路上好用的現有程式庫

補充: 外掛程式 (實現合併列印功能)

補充：JavaScript的日期相關處理函式

tags: `Google Apps Script` `產業人才投資方案` `授課資料`