今天的目標：

要怎麼針對特定資料，固定地創造圖表？現在用到圖表的機會越來越多，很多時候我們會需要創造大量的圖表並做成報告。今天就要帶大家知道，怎麼樣用 GAS 讓你一鍵自動化圖表創造的過程。理論上會有這樣的結果——

那今天的關鍵問題是——

Q1. 如何把 Google Sheet 的資料自動轉為 Google Slide 上的圖表？

今天想跟各位分享的場景主要是「大量製圖」因為考量到大家都有不同的資料，思考後選擇用之前一個複雜度夠的範本資料，來讓大家可以應用。今天鎖定的是，針對某個複雜資料創造「散佈圖」。那就讓我們開始吧！

如何把 Google Sheet 的資料自動轉為 Google Slide 上的圖表？

Input

• 在 Google 試算表（Spreadsheet）上有著零散在不同的資料表（Sheet 或叫做 Tab) 的資料
  • 資料表數不超過兩百
  • 每一個資料表的第一列都是 Header
  • 每一個資料表的總列數不超過一百筆
  • 每一個資料表對只有有兩欄
  • 每一筆資料都是「正數數值」（浮點數）或「空值」

資料長這樣——

舉例方便，我這邊就只展示三個圖表，但要更多基本上只要調整參數即可。

Output

• 在其中一頁 Sheet 上的散佈圖（Scatter）圖，同時放在 Google Slides 上
  • 每個散佈圖都有輔助線
  • 每個點都要標註
  • 縱軸與橫軸都有標上

釐清了 Input 與 Output，就讓我們開始吧！

Step 1 從 Google Sheet 進入 GAS 並設定指定資料夾 ID

今天我們用 Google Sheet 作為連結 GAS 的管道，讓我們借用 D14 的影片。

一樣第一次按下 GAS 中的「執行」會有「存取驗證」需要大家按一下。這邊仍是借用一下 D2 的影片。

接著，我們就來讀取表單們。

Step 2 讀取不同 Sheets 上的資料們

首先，我們要抓出所有的 Sheets，這邊我們透過 getSheets() 來達到。這邊我們搭配一個簡單的程式，來看怎麼抓到每個試算表的名稱。

function getSheetsName(){
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  for (let i = 0; i < sheets.length; i ++){
    Logger.log(sheets[i].getName())
  }
}

來檢查一下程式碼——

好，那當可以讀到每個資料表後，就是個別讀出每個資料表的數值了。

function getSheetData(){
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  for (let i = 0; i < sheets.length; i ++){
    let sheet = sheets[i];
    Logger.log(sheet.getName())
    let start_row = 2;
    let start_col = 1;
    let num_row = sheet.getLastRow() - 1;
    let num_col = 2;
    let data = sheet.getRange(start_row, start_col, num_row, num_col).getValues();
    Logger.log(data)

  }
}

跑起來長這樣——

影片最後，是在透過第三張試算表的最後幾個資料來核對是否正確，核對 Log 跑出來的最後幾筆資料長這樣——

[2.0229, 17.96081539], [17.9211, 5.843724838], [2.702, 39.47161016], [100.0, 3.015760309], [, 1.07E-7]]

對照影片，初步研判是有抓到對的數值與位置是正確的。

好，那我們都抓到資料了，要怎麼生成圖表？

Step 3 針對讀取到的數值製作圖表們

要用 Google Sheet 做圖表，我們要用到的是 chart 的物件。

這邊有分兩個方式，一個是直接用 Charts.newDataTable() 創造一個圖表，另一個則是直接用 sheet.newChart()。我個人建議是搭配後者，因為可以直接插入表單，比較好核對。

如果想知道更細節，用 chart 所生出的物件，並不能直接轉入 Google Sheet，因為 Google Sheet 接受的是 EmbeddedChart，需要透過綁定特定的 Sheet 搭配 .newChart() 來生成。簡單來說，在程式碼的世界中，並不是能直接通用的兩個物件。

那一樣先上程式碼，再來講解

function getDataAndBuildChart(){
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  let output_sheet = ss.getSheetByName('結果');
  for (let i = 1; i < sheets.length; i ++){
    let input_sheet = sheets[i-1];
    let input_sheet_name = input_sheet.getName();
    let start_row = 2;
    let start_col = 1;
    let num_row = input_sheet.getLastRow() - 1;
    let num_col = 2;
    let data_range = input_sheet.getRange(start_row, start_col, num_row, num_col);

    let chart = input_sheet.newChart()
                .setChartType(Charts.ChartType.SCATTER)
                .addRange(data_range)
                .setOption("hAxis", {title: input_sheet.getRange(1,1).getValue()})
                .setOption("vAxis", {title: input_sheet.getRange(1,2).getValue()})
                .setOption('trendlines', {0:{type:"linear"}})
                .setOption('title', `${input_sheet_name}'s result'`)
                .setPosition(10, i*5, 0, 0)
                .build();

    output_sheet.insertChart(chart);
  }
}

我們來看生成的影片——

回到程式碼，一路到 let chart 主要在我們之前的內容有說明，今天專注說明 chart 的部分。

1. 首先，先針對綁定的 sheet 創造表單 （newChart()）。為了方便理解，我將名稱改用 input_sheet ，要放的表單叫做 output_sheet。當然要生成的圖表，不一定要綁定這份 Sheet 才能用裡面的資料。但綁定要用資料的 Sheet 會讓我們的程式碼比較簡單。
2. 再來，用 Charts.CharType 來設定散佈圖 （SCARRTER），其他圖表種類可以參考此連結，基本上在 Google 上面的圖都有，只是要找到對應的英文以及放資料的形式就是。
3. 接著設定這張圖表的相關資料，其中 hAxis 表示橫軸（horizontal Axis）、vAxis 表示縱軸（vertical Axis），另外就是標題與趨勢線。每個圖表都有自己的「選項」，可以參照這份清單
4. 透過 setPosition() 來設定位置。基本上 position 裡面的數值，直接是在問你「第幾格」（Cell），可以參照下圖。

5. 最後，一定要按個 Build() 後才算是生成完成，最後看是要在哪裡插入表格，就用 insertChart ，如果沒有設定 insertChart 那表格就不會生出來。此外，如果沒有在第四點設定 setPosition()，就會預設全部疊在一起。

接著，就是讓入 Google Slides 了。

Step 4 將圖表們放到 Google Slides 中

要操作額外的 Google Slides，首先要抓出 ID，借一下之前的影片們，這邊我們用 D21 的「自動化爲鐵人賽的每一篇貼文生封面圖」 的結果 Slides 為例。抓出 ID 的示範如下——

並將這 ID 到我們的 GAS 當中設定為一個參數。

var target_slide_ID = "your_pres_ID_here"

做出 ID 的影片如下——

抓到 ID 後就是要依據圖表新增到 Google Slide 中。這邊要說明一下，如果要將圖表放入從 Google Sheet 放入 Google Slides，就一定要從 Google Sheet 抓（正式的物件名稱叫 sheetchart），也就是說我不能還沒有用 Step 3 的 insertChart 就放入 Google Slide，會失敗。當然，也有種做法是把原本的圖表用 .getAs('image/png')轉為圖片，再插入圖片，但這樣就不會有跟 Google Sheet 的連動效果就是，要取捨一下。

好，那我們稍微改一下程式碼，主要新增 slide.insertSheetsChart() 的部分。

function getDataAndBuildChartToSlides(){
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  let output_sheet = ss.getSheetByName('結果');
  for (let i = 1; i < sheets.length; i ++){
    let input_sheet = sheets[i-1];
    let input_sheet_name = input_sheet.getName();
    let start_row = 2;
    let start_col = 1;
    let num_row = input_sheet.getLastRow() - 1;
    let num_col = 2;
    let data_range = input_sheet.getRange(start_row, start_col, num_row, num_col);

    let chart = input_sheet.newChart()
                .setChartType(Charts.ChartType.SCATTER)
                .addRange(data_range)
                .setOption("hAxis", {title: input_sheet.getRange(1,1).getValue()})
                .setOption("vAxis", {title: input_sheet.getRange(1,2).getValue()})
                .setOption('trendlines', {0:{type:"linear"}})
                .setOption('title', `${input_sheet_name}'s result'`)
                .setPosition(10, i*5, 0, 0)
                .build();

    output_sheet.insertChart(chart);
  }

  let charts = output_sheet.getCharts();
  let pres = SlidesApp.openById(target_slide_id);
  for(chart of charts){
    let new_slide = pres.appendSlide();
    new_slide.insertSheetsChart(chart)
  }
}

讓我們看一下跑出來的影片——

當然要在 Google Slides 加上其他文字說明也可以，詳情可以參考D22 - 如何用 Apps Script 自動化地創造與客製 Google Slides？ D22 -（三）一次看完所有檔案的預覽 。或是想換成結合其他 Google 系列產品，不管是用 Gmail 寄出（可以參考 D4 - GAS 整合 Google Form / Google Sheet 並自動寄出客製的 Email）、Google Form（D11 -（ㄧ）複製並客製你的 Google Form 與 Google Docs （D15 -（二）快速生出大量寄件信封資料。

今天是我們的第 24 天，盤點一下今天學的

1. 三種 Chart 型態：
   1. EmbeddedChart ：要放入 Google Sheet 要用的表單，透過 sheet.newChart() 建置
   2. SheetChart：從既存的 Google Sheet 中讀取的表單，透過 sheet.getCharts() 取得
   3. Chart GAS 直接操作 Chart 的 API，相對進階，在 GAS 中直接透過 Chart 取得。
2. Google Sheet 中圖表（散佈圖）的建置方式
3. 將 Google Sheet 中圖表轉入 Google Slides 的方式。

那今天就到這邊，鐵人賽也接近了尾聲。也進入了最關鍵的 Sheet 的部分，希望內容對大家有所幫助。如果還有問題，透過留言之外，也可以到 Facebook Group，想開很久這次鐵人賽才真的開起來，歡迎來當 Founding Member。如果不想錯過可以訂閱按讚小鈴鐺（？），也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。