D24 - 如何用 Apps Script 自动化地创造与客制 Google Sheet？（ㄧ）自动化创造图表并放到报告中

今天的目标：

要怎麽针对特定资料，固定地创造图表？现在用到图表的机会越来越多，很多时候我们会需要创造大量的图表并做成报告。今天就要带大家知道，怎麽样用 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。如果不想错过可以订阅按赞小铃铛（？），也欢迎留言跟我说你还想知道什麽做法/主题。我们明天见。