Google Apps Script｜打造每日数据报表自动化 RPA｜快速串接 Google Analytics 与 Google Sheet

Logger.log("Hi")

class HeaderStyle {
  constructor() {
    this.color = "#ffffff";
    this.backgroundColor = "#e3284b";
    this.bold = false;
    this.size = 12;
    this.horizontalAlignment = "center";
    this.verticalAlignment = "middle";
  }
}

class ContentStyle {
  constructor() {
    this.color = "#000000";
    this.backgroundColor = "#ffffff";
    this.bold = false;
    this.size = 12;
    this.horizontalAlignment = "center";
    this.verticalAlignment = "middle";
  }
}

class HeaderDateStyle {
  constructor() {
    this.color = "#ffffff";
    this.backgroundColor = "#001a40";
    this.bold = true;
    this.size = 12;
    this.horizontalAlignment = "center";
    this.verticalAlignment = "middle";
  }
}

class DailyReportField {
  constructor(name, headerStyle, contentStyle, format = null, value = null) {
    this.name = name;
    this.headerStyle = headerStyle;
    this.contentStyle = contentStyle;
    this.format = format;
    this.value = value;
  }
}

class DailyReport {
  constructor(sheetID, date) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.date = date;

    this.sheetFields = [
      new DailyReportField("日期", new HeaderDateStyle(), new HeaderDateStyle()),
      new DailyReportField("星期", new HeaderDateStyle(), new HeaderDateStyle()),
      new DailyReportField("日流量", new HeaderStyle(), new ContentStyle(), "#,##0", '=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&4)+INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&5)'), // =4(PC 流量) + 5(Mobile 流量)
      new DailyReportField("PC 流量", new HeaderStyle(), new ContentStyle(), "#,##0"),
      new DailyReportField("Mobile 流量", new HeaderStyle(), new ContentStyle(), "#,##0"),
      new DailyReportField("注册数", new HeaderStyle(), new ContentStyle(), "#,##0")
    ]

    // 日流量公式分解说明：
    // 1. COLUMN() 函数会回传目前单元格的列号码。
    // 2. ADDRESS(1, COLUMN(), 4) 会以给定的行数（`COLUMN()` 的结果）和固定的列数（1）来生成一个绝对引用的地址。其中，第三个参数 4 表示不带任何美元符号（$）的相对地址。比如，如果你在第三列的任何单元格中使用这个函数，它会回传 "C1"。
    // 3. SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") 会将 ADDRESS 函数产生的地址中的数字 1 移除，因此只保留了列的英文字母，比如："C"。
    // 4. INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & 4) 这边 & 4 其实应该是 &4。`SUBSTITUTE` 函数的结果会和数字 4 连接，形成 "C4" 这样的字符串，然后 INDIRECT 函数会将这个字符串转换成对应的单元格引用。所以，如果你在列 C 中的任何单元格使用此公式，它会引用 C4。
    // 5. 类似地，`INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & 5)` 会引用相同列的第五行的单元格。比如，如果你在列 C 中的任何单元格使用此公式，它会引用 C5。
    // 6. 最后，这两个 INDIRECT 函数引用的单元格值相加。
  }

  execute() {
    const sheet = this.getSheet();

  }

  // 取得给定日期的目标 Sheet
  getSheet() {
    // 以月来区分 Sheet，找到当月 Sheet
    var thisMonthSheet = this.separateSheet.getSheetByName(this.getSheetName());
    if (thisMonthSheet == null) {
      // 无则建立新的月 Sheet
      thisMonthSheet = this.makeMonthSheet();
    }

    return thisMonthSheet;
  }

  // 月 Sheet 命名规则
  getSheetName() {
    return Utilities.formatDate(this.date, "GMT+8", "yyyy-MM");
  }

  // 建立新的月 Sheet
  makeMonthSheet() {
    // 新增当月 Sheet，移动到第一个位子
    var thisMonthSheet = this.separateSheet.insertSheet(this.getSheetName(), {index: 0});
    thisMonthSheet.activate();
    this.separateSheet.moveActiveSheet(1);

    // 新增第一栏，栏位名称、设定 Pinned、宽度 200
    thisMonthSheet.insertColumnsBefore(1, 1);
    thisMonthSheet.setFrozenColumns(1);
    thisMonthSheet.setColumnWidths(1, 1, 200);

    // 填充栏位名称
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const text = sheetField.name;
      const style = sheetField.headerStyle;
      
      const range = thisMonthSheet.getRange(parseInt(currentRow) + 1, 1);
      this.setContent(range, text, style);
      range.setHorizontalAlignment("left");
    }

    // 设定栏高
    thisMonthSheet.setRowHeights(1, Object.keys(this.sheetFields).length, 30);

    // 设定 Pinned 第一、二列(日期、星期)
    thisMonthSheet.setFrozenRows(2);

    // 新增总计栏位
    thisMonthSheet.insertColumnsAfter(thisMonthSheet.getLastColumn(), 1); // 新增一栏在最后一栏
    const summaryColumnIndex = thisMonthSheet.getLastColumn() + 1;

    // 填充总计栏位
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const summaryRowIndex = parseInt(currentRow) + 1;

      const range = thisMonthSheet.getRange(summaryRowIndex, summaryColumnIndex);
      const style = sheetField.contentStyle;

      if (summaryRowIndex == 1) {
        // 日期...
        this.setContent(range, "总计", style);
      } else if (summaryRowIndex == 2) {
        // 星期...merge...
        const mergeRange = thisMonthSheet.getRange(1, summaryColumnIndex, summaryRowIndex, 1);
        this.setContent(mergeRange, "总计", style);
        mergeRange.merge();
      } else {
        this.setContent(range, '=IFERROR(SUM(INDIRECT(SUBSTITUTE(ADDRESS(1, 1, 4), "1", "") & '+summaryRowIndex+'):INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN() - 1, 4), "1", "") & '+summaryRowIndex+')), 0)', style);

        // 1. IFERROR(value, [value_if_error]) 函数用于判断公式中是否存在错误，并在有错误时返回指定的值。它接受两个参数：`value` 是需要计算的表达式或函数，`value_if_error` 是当 value 发生错误时返回的值。在这个情境下，如果 SUM 函数中的计算发生错误，则返回 0。
        // 2. SUM(range) 函数用于计算范围内所有数字的总和。
        // 3. INDIRECT(ref_text, [is_A1_notation]) 函数将文字字串转换成单元格引用。这里使用 INDIRECT 函数是为了动态生成所需的参照范围。
        // 4. SUBSTITUTE(text, old_text, new_text, [instance_num]) 函数在文字字串中替换指定的文字。这里使用 SUBSTITUTE 是为了将 ADDRESS 函数回传的地址中的 "1" 替换成其他内容。
        // 5. ADDRESS(row, column, [abs_num], [a1], [sheet]) 函数根据给定的行列号码回传对应的单元格地址。在这里，ADDRESS(1, 1, 4) 生成了第一行第一列的单元格地址，但是由于 abs_num 是 4，所以地址不包含工作表名称和固定符号 $。类似地，`ADDRESS(1, COLUMN() - 1, 4)` 产生了从第一行到当前列前一列的单元格地址。
        // 6. COLUMN() 函数回传当前单元格的列号。
        // 7. summaryRowIndex = 所在列     

      }
    }

    return thisMonthSheet;
  }

  setContent(range, text, style) {
    if (String(text) != "") {
      range.setValue(text);
    }

    range.setBackgroundColor(style.backgroundColor);
    range.setFontColor(style.color);

    if (style.bold) {
      range.setFontWeight("bold");
    }

    range.setHorizontalAlignment(style.horizontalAlignment);
    range.setVerticalAlignment(style.verticalAlignment);
    range.setFontSize(style.size);
    range.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  }
}

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE"
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

function debug() {
  var report = new DailyReport(targetGoogleSheetID, new Date());
  report.execute();
}

// Remeber add Google Analytics Data API to Services, or you'll see this error: ReferenceError: AnalyticsData is not defined
// GA Debug Toole: https://ga-dev-tools.web.app/ga4/query-explorer/

class GAData {
  constructor(date) {
    this.date = date;

    const traffic = this.fetchGADailyUsage();
    this.pc_traffic = traffic["desktop"];
    this.mobile_traffic = traffic["mobile"];
  }

  fetchGADailyUsage() {
    const dimensionPlatform = AnalyticsData.newDimension();
    dimensionPlatform.name = "deviceCategory";

    const metric = AnalyticsData.newMetric();
    metric.name = "sessions";

    const dateRange = AnalyticsData.newDateRange();
    // 预设查询给定日范围内资料 e.g. 2024-01-01 ~ 2024-01-01
    dateRange.startDate = this.getDateString();
    dateRange.endDate = this.getDateString();

    // Filter Example:
    // const filterExpression = AnalyticsData.newFilterExpression();
    // const filter = AnalyticsData.newFilter();
    // filter.fieldName = "landingPagePlusQueryString";
    // const stringFilter = AnalyticsData.newStringFilter()
    // stringFilter.value = "/life\\|/article\\|/chat\\|/house\\|/event/230502\\|/event/230310";
    // stringFilter.matchType = "PARTIAL_REGEXP";
    // filter.stringFilter = stringFilter;
    // filterExpression.filter = filter;

    const request = AnalyticsData.newRunReportRequest();
    request.dimensions = [dimensionPlatform];
    request.metrics = [metric];
    request.dateRanges = dateRange;

    // Filter Example:
    // const filterExpression = AnalyticsData.newFilterExpression();
    // filterExpression.expression = filterExpression;
    // request.dimensionFilter = filterExpression;
    // or Not
    // const notFilterExpression = AnalyticsData.newFilterExpression();
    // notFilterExpression.notExpression = filterExpression;
    // request.dimensionFilter = notFilterExpression;

    const report = AnalyticsData.Properties.runReport(request, "properties/" + gaPropertyId).rows;
    // 无资料
    if (report == undefined) {
      return {"desktop": 0, "mobile": 0};
    }

    // [{metricValues=[{value=4517}], dimensionValues=[{value=mobile}]}, {metricValues=[{value=3189}], dimensionValues=[{value=desktop}]}, {metricValues=[{value=63}], dimensionValues=[{value=tablet}]}]

    var result = {};
    report.forEach(function(element) {
      result[element.dimensionValues[0].value] = element.metricValues[0].value;
    });

    return result;
  }

  getDateString() {
    return Utilities.formatDate(this.date, "GMT+8", "yyyy-MM-dd");
  }
}

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  var report = new DailyReport(targetGoogleSheetID, new Date());
  report.execute();
  //
  var gaData = new GAData(new Date());
  Logger.log(gaData);
}

class DailyReport {
  constructor(sheetID, date, gaData, inHouseReportData) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.date = date;

    const dateString = Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd");
    const weekString = ["星期日", "星期一","星期二","星期三","星期四","星期五","星期六"][date.getDay()]; // 取得星期，星期日为0，星期一为1，以此类

    this.sheetFields = [
      new DailyReportField("日期", new HeaderDateStyle(), new HeaderDateStyle(), null, dateString),
      new DailyReportField("星期", new HeaderDateStyle(), new HeaderDateStyle(), null, weekString),
      new DailyReportField("日流量", new HeaderStyle(), new ContentStyle(), "#,##0", '=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&4)+INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&5)'), // =4(PC 流量) + 5(Mobile 流量)
      new DailyReportField("PC 流量", new HeaderStyle(), new ContentStyle(), "#,##0", gaData.pc_traffic),
      new DailyReportField("Mobile 流量", new HeaderStyle(), new ContentStyle(), "#,##0", gaData.mobile_traffic),
      new DailyReportField("注册数", new HeaderStyle(), new ContentStyle(), "#,##0", inHouseReportData.registers)
    ]
  }

  execute() {
    const sheet = this.getSheet();
    const dateColumnIndex = this.makeOrGetDateColumn(sheet); // 取得已存在的更新或建立新的栏位

    // 填充栏位内容
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const rowIndex = parseInt(currentRow) + 1;

      if (rowIndex != null) {
        const range = sheet.getRange(rowIndex, dateColumnIndex);
        const text = sheetField.value;
        const style = sheetField.contentStyle;
        this.setContent(range, text, style);
        this.setFormat(range, sheetField.format);          
      }
    }
  }

  // 取得给定日期的目标 Sheet
  getSheet() {
    // 以月来区分 Sheet，找到当月 Sheet
    var thisMonthSheet = this.separateSheet.getSheetByName(this.getSheetName());
    if (thisMonthSheet == null) {
      // 无则建立新的月 Sheet
      thisMonthSheet = this.makeMonthSheet();
    }

    return thisMonthSheet;
  }

  // 月 Sheet 命名
  getSheetName() {
    return Utilities.formatDate(this.date, "GMT+8", "yyyy-MM");
  }

  // 建立新的月 Sheet
  makeMonthSheet() {
    // 新增当月 Sheet，移动到第一个位子
    var thisMonthSheet = this.separateSheet.insertSheet(this.getSheetName(), {index: 0});
    thisMonthSheet.activate();
    this.separateSheet.moveActiveSheet(1);

    // 新增第一栏，栏位名称、设定 Pinned、宽度 200
    thisMonthSheet.insertColumnsBefore(1, 1);
    thisMonthSheet.setFrozenColumns(1);
    thisMonthSheet.setColumnWidths(1, 1, 200);

    // 填充栏位名称
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const text = sheetField.name;
      const style = sheetField.headerStyle;
      
      const range = thisMonthSheet.getRange(parseInt(currentRow) + 1, 1);
      this.setContent(range, text, style);
      range.setHorizontalAlignment("left");
    }

    // 设定栏高
    thisMonthSheet.setRowHeights(1, Object.keys(this.sheetFields).length, 30);

    // 设定 Pinned 第一、二列(日期、星期)
    thisMonthSheet.setFrozenRows(2);

    // 新增总计栏位
    thisMonthSheet.insertColumnsAfter(thisMonthSheet.getLastColumn(), 1); // 新增一栏在最后一栏
    const summaryColumnIndex = thisMonthSheet.getLastColumn() + 1;

    // 填充总计栏位
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const summaryRowIndex = parseInt(currentRow) + 1;

      const range = thisMonthSheet.getRange(summaryRowIndex, summaryColumnIndex);
      const style = sheetField.contentStyle;

      if (summaryRowIndex == 1) {
        // 日期...
        this.setContent(range, "总计", style);
      } else if (summaryRowIndex == 2) {
        // 星期...merge...
        const mergeRange = thisMonthSheet.getRange(1, summaryColumnIndex, summaryRowIndex, 1);
        this.setContent(mergeRange, "总计", style);
        mergeRange.merge();
      } else {
        this.setContent(range, '=IFERROR(SUM(INDIRECT(SUBSTITUTE(ADDRESS(1, 1, 4), "1", "") & '+summaryRowIndex+'):INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN() - 1, 4), "1", "") & '+summaryRowIndex+')), 0)', style);
      }
    }

    return thisMonthSheet;
  }

  // 建立或取得日期的栏位
  // 从最近一日往后加一栏位
  makeOrGetDateColumn(sheet) {
    const firstRowColumnsRange = sheet.getRange(1, 1, 1, sheet.getLastColumn()); // 取得第一列(日期)资料范围
    const firstRowColumns = firstRowColumnsRange.getValues()[0]; // 取得资料范围的值 0 = 第一列
    
    var columnIndex = firstRowColumns.findIndex((date) => (date instanceof Date && Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd") == Utilities.formatDate(this.date, "GMT+8", "yyyy/MM/dd"))); // 找到对应日期的栏位 Index

    if (columnIndex < 0) {
      // Not Found，找到前一日的位子
      var preDate = new Date(this.date);
      preDate.setDate(preDate.getDate() - 1);

      while(preDate.getMonth() == this.date.getMonth()) {
        columnIndex = firstRowColumns.findIndex((date) => (date instanceof Date && Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd") == Utilities.formatDate(preDate, "GMT+8", "yyyy/MM/dd")));
        if (columnIndex >= 0) {
          break;
        }

        preDate.setDate(preDate.getDate() - 1);
      }

      if (columnIndex >= 0) {
        columnIndex += 1;
        sheet.insertColumnsAfter(columnIndex, 1); // 在前一日栏位后新增一栏
        columnIndex += 1;
      }
    } else {
      columnIndex += 1;
    }

    if (columnIndex < 0) {
        sheet.insertColumnsAfter(1, 1); // 默认，直接新增一栏在第一栏后面
        columnIndex = 2;
    } 

    // 设定栏位宽度
    sheet.setColumnWidths(columnIndex , 1, 100);

    return columnIndex
  }

  // 设定栏位 Format 样式
  setFormat(range, format) {
    if (format != null) {
      range.setNumberFormat(format);
    }
  }

  // 填充内容到栏位
  setContent(range, text, style) {
    if (String(text) != "") {
      range.setValue(text);
    }

    range.setBackgroundColor(style.backgroundColor);
    range.setFontColor(style.color);

    if (style.bold) {
      range.setFontWeight("bold");
    }

    range.setHorizontalAlignment(style.horizontalAlignment);
    range.setVerticalAlignment(style.verticalAlignment);
    range.setFontSize(style.size);
    range.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  }
}

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  const date = new Date();
  const gaData = new GAData(date);
  const inHouseReportData = fetchInHouseReportData(date);
  
  const report = new DailyReport(targetGoogleSheetID, date, gaData, inHouseReportData);
  report.execute();
  
}

// 模拟部分数据资料可能是打其他平台 API 取得的。
function fetchInHouseReportData(date) {
  // EXMAPLE REQUEST:
  // var options = {
  //   'method' : 'get',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   }
  // };
  // OR
  // var options = {
  //   'method' : 'post',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   },
  //   'payload' : data
  // };

  // var res = UrlFetchApp.fetch(url, options);
  // const result = JSON.parse(res.getContentText());

  // REMEMBER, DUE TO SECURITY REASON, We can't customize user-agent.
  
  return {"registers": Math.floor(Math.random() * (180 - 30 + 1)) + 30} // MOCK DATA random 30~180
}

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  cronjob();
}

// 照现实状况来说，通常是今日查昨日数据，才是完整的资料
function cronjob() {
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);

  const gaData = new GAData(yesterday);
  const inHouseReportData = fetchInHouseReportData(yesterday);
  
  const report = new DailyReport(targetGoogleSheetID, yesterday, gaData, inHouseReportData);
  report.execute();
}

// 模拟部分数据资料可能是打其他平台 API 取得的。
function fetchInHouseReportData(date) {
  // EXMAPLE REQUEST:
  // var options = {
  //   'method' : 'get',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   }
  // };
  // OR
  // var options = {
  //   'method' : 'post',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   },
  //   'payload' : data
  // };

  // var res = UrlFetchApp.fetch(url, options);
  // const result = JSON.parse(res.getContentText());

  // REMEMBER, DUE TO SECURITY REASON, We can't customize user-agent.
  
  return {"registers": Math.floor(Math.random() * (180 - 30 + 1)) + 30} // MOCK DATA random 30~180
}

class WebDailyReport {
  constructor(sheetID, dayCount) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.dayCount = dayCount;
    this.sheetRows = [
      "日期",
      "星期",
      "日流量",
      "PC 流量",
      "Mobile 流量",
      "注册数"
    ];
  }

  allData(startDate) {
    var sheetRowsIndexs = {};
    var count = this.dayCount;
    var result = [];
    while (count >= 0) {
      const preDate = new Date(startDate);
      preDate.setDate(preDate.getDate() - (this.dayCount - count));
      const sheetName = Utilities.formatDate(preDate, "GMT+8", "yyyy-MM");
      const targetSheet = this.separateSheet.getSheetByName(sheetName);
      if (targetSheet != null) {
        const firstRowColumnsRange = targetSheet.getRange(1, 1, 1, targetSheet.getLastColumn()); // 取得第一列(日期)资料范围
        const firstRowColumns = firstRowColumnsRange.getValues()[0]; // 取得资料范围的值 0 = 第一列
        var columnIndex = firstRowColumns.findIndex((date) => (date instanceof Date && Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd") == Utilities.formatDate(preDate, "GMT+8", "yyyy/MM/dd"))); // 找到对应日期的栏位 Index
        
        if (columnIndex >= 0) {
          columnIndex = parseInt(columnIndex) + 1;
          if (sheetRowsIndexs[sheetName] == undefined \\|\\| sheetRowsIndexs[sheetName] == null) {
            sheetRowsIndexs[sheetName] = this.sheetRows.map((sheetRow) => this.getFieldRow(targetSheet, sheetRow));
          }

          if (result.length == 0) {
            // 新增第一栏
            const ranges = sheetRowsIndexs[sheetName].map((rowIndex) => (rowIndex != null) ? (targetSheet.getRange(rowIndex, 1)) : (null));
            result.push(this.makeValues(ranges));
          }

          const ranges = sheetRowsIndexs[sheetName].map((rowIndex) => (rowIndex != null) ? (targetSheet.getRange(rowIndex, columnIndex)) : (null));
          result.push(this.makeValues(ranges));
        }
      }

      count -= 1;
    }

    var transformResult = {};
    for (const columnIndex in result) {
      for (const rowIndex in result[columnIndex]) {
        if (transformResult[rowIndex] == undefined) {
          transformResult[rowIndex] = [];
        }

        if (columnIndex == 0) {
          transformResult[rowIndex].unshift(result[columnIndex][rowIndex]);
        } else {
          transformResult[rowIndex].splice(1, 0, result[columnIndex][rowIndex]);
        }
        
      }
    }

    return transformResult;
  }

  // 转换 栏位属性 值成 显示用物件
  makeValues(ranges) {
    const data = ranges.map((range) => (range != null) ? (range.getDisplayValues()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const backgroundColors = ranges.map((range) => (range != null) ? (range.getBackgrounds()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const colors = ranges.map((range) => (range != null) ? (range.getFontColorObjects()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const sizes = ranges.map((range) => (range != null) ? (range.getFontSizes()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const bolds = ranges.map((range) => (range != null) ? (range.getFontWeights()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const horizontalAlignments = ranges.map((range) => (range != null) ? (range.getHorizontalAlignments()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const verticalAlignments = ranges.map((range) => (range != null) ? (range.getVerticalAlignments()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));

    var result = [];
    for(const index in data) {
        const row = data[index];
        result.push({
          "value": row,
          "backgroundColor": backgroundColors[index],
          "color": this.colorStripper(colors[index]?.asRgbColor()?.asHexString()),
          "size": sizes[index],
          "bold": bolds[index],
          "horizontalAlignment": this.alignConventer(horizontalAlignments[index]),
          "verticalAlignment": verticalAlignments[index]
        });
    }

    return result;
  }

  colorStripper(colorString) {
    if (colorString == undefined \\|\\| colorString == null) {
      return null
    }

    if (colorString.length == 9) {
      return "#"+colorString.substring(3, 9);
    } else {
      return colorString;
    }
  }

  alignConventer(horizontalAlignment) {
    if (horizontalAlignment == undefined \\|\\| horizontalAlignment == null) {
      return null
    }

    return horizontalAlignment.replace('general-', '')
  }

  getFieldRow(sheet, name) {
    const firstColumnRowsRange = sheet.getRange(1, 1, sheet.getLastRow(), 1); // 取得第一栏(栏位)资料范围
    const firstColumnRows = firstColumnRowsRange.getValues(); // 取得资料范围的值
    const foundIndex = firstColumnRows.findIndex((firstColumnRow) => firstColumnRow[0] == name);

    if (foundIndex < 0) {
      return null;
    } else {
      return foundIndex + 1;
    }
  }
}

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  cronjob();
}

function cronjob() {
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);

  const gaData = new GAData(yesterday);
  const inHouseReportData = fetchInHouseReportData(yesterday);
  
  const report = new DailyReport(targetGoogleSheetID, yesterday, gaData, inHouseReportData);
  report.execute();
}

function doGet(e) {
  return HtmlService.createTemplateFromFile('Web_DailyReport_ Scaffolding').evaluate();
}

function getDailyReportBody() {
  const html = HtmlService.createTemplateFromFile('Web_DailyReport_Body').evaluate().getContent();
  return html;
}

// FOR POST
// function doPost(e) {
//  ref: https://developers.google.com/apps-script/guides/web?hl=zh-tw
// }


// 模拟部分数据资料可能是打其他平台 API 取得的。
function fetchInHouseReportData(date) {
  // EXMAPLE REQUEST:
  // var options = {
  //   'method' : 'get',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   }
  // };
  // OR
  // var options = {
  //   'method' : 'post',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   },
  //   'payload' : data
  // };

  // var res = UrlFetchApp.fetch(url, options);
  // const result = JSON.parse(res.getContentText());

  // REMEMBER, DUE TO SECURITY REASON, We can't customize user-agent.
  
  return {"registers": Math.floor(Math.random() * (180 - 30 + 1)) + 30} // MOCK DATA random 30~180
}

// Constant variables
const notionToken = "XXXXX";
const safeToken = "XXXXX";

function doGet(e) {
  const ics = HtmlService.createTemplateFromFile('ics');

  if (e.parameter.token != safeToken) {
    return ContentService.createTextOutput("Access Denied!");
  }

  ics.events = getQuickNote();
  
  return ContentService.createTextOutput(ics.evaluate().getContent()).setMimeType(ContentService.MimeType.ICAL);
}

function debug() {
  const ics = HtmlService.createTemplateFromFile('ics');
  ics.events = getQuickNote();
  Logger.log(ics.evaluate().getContent());
}

function getQuickNote() {
  // YOUR FILTER Condition:
  const payload = {
    "filter": {
      "and": [
        {
          "property": "Date",
            "date": {
            "is_not_empty": true
          }
        }
        ,
        {
          "property": "Name",
            "title": {
            "is_not_empty": true
          }
        }
      ]
    }
  };
  const result = getDatabase(YOUR_DATABASE_ID, payload);
  var events = [];
  for (const index in result.results) {
    const item = result.results[index]
    const properties = item.properties;

    const id = item['id'];
    const create = toICSDate(item["created_time"]);
    const edit = toICSDate(item["last_edited_time"]);
    const startDate = properties['Date']['date']['start'];
    const start = toICSDate(startDate);
    var endDate = properties['Date']?.['date']?.['end'];
    if (endDate == null) {
      endDate = startDate;
    }
    const end = toICSDate(endDate);
    const type = properties['Type']?.['multi_select']?.[0]?.['name'];

    const title = "["+type+"] "+properties?.['Name']?.['title']?.[0]?.['plain_text'];
    const description = item['url'];
    
    events.push(
      {
        "id":id,
        "create":create,
        "edit":edit,
        "start":start,
        "end":end,
        "title":title,
        "description":description
      }
    )
  }
  return events;
}
// TO UTC Date
function toICSDate(date) {
  const icsDate = new Date(date);
  icsDate.setHours(icsDate.getHours() - 8);
  return Utilities.formatDate(icsDate, "GMT+8", "yyyyMMdd'T'HHmmss'Z'");// 20240304T132300Z
}

// Notion
function getDatabase(id, payload) {
  const url = 'https://api.notion.com/v1/databases/'+id+'/query/';
  const options = {
    method: 'post',
    headers: {
      'Authorization': 'Bearer '+notionToken,
      'Content-Type': 'application/json',
      'Notion-Version': '2022-06-28'
    },
    payload: JSON.stringify(payload)
  }; 
  const result = UrlFetchApp.fetch(url, options);
  return JSON.parse(result.getContentText());
}