如何基于JSON生成数组并通过Google Apps Script向Google Sheet批量写入多行数据

我正在尝试将Google Search Console API的数据导入Google Sheet，目前已成功获取到所需的JSON格式数据，但无法将数据正确写入表格多行，当前编写的函数仅能将所有数据写入单行，代码如下：

function dataFillTest(sheet, json, regex) {
  sheet.clear();
  sheet.appendRow(["Date", "Link", "Clicks", "Category"]);

  var data = new Array();

  for (var i = 0; i < 5; i++) {
      data.push(json.rows[i].keys[0], json.rows[i].keys[1], json.rows[i].clicks);
  }
  Logger.log("Contents of data after for loop");
  Logger.log(data);
  
  var lastRow = sheet.getLastRow();
  sheet.getRange(lastRow + 1, 1, 1, 15).setValues([data]);

  var cell = sheet.getRange("D2:D");
  cell.setFormula('=REGEXEXTRACT(B2; "https://'+ regex+ '(\\w+)")');
}

运行上述代码后，所有数据会挤在表头下的同一行，5条数据的15个字段平铺在A2到O2列。
我需要实现的效果是5条数据分5行展示，每行分别对应日期、链接、点击量、自动提取的分类四个字段。
供参考的源JSON数据结构如下：

{
  responseAggregationType: 'byPage',
  rows: [
    {
      ctr: 0.2625019822003836,
      keys: ['2021-08-26', 'https://mondo.rs/Magazin/Zdravlje/a1522777/Letovanje-u-Crnoj-Gori-i-stomacni-problemi.html'],
      clicks: 120842,
      impressions: 460347
    },
    {
      ctr: 0.18834311984734511,
      keys: ['2021-09-13', 'https://mondo.rs/Magazin/Stil/a1528560/Hrana-za-jaci-imunitet-posle-50.html'],
      clicks: 102256,
      impressions: 542924
    },
    {
      ctr: 0.23844989249500642,
      keys: ['2021-09-07', 'https://mondo.rs/Magazin/Zdravlje/a1527110/Novi-organ-u-ljudskom-telu.html'],
      clicks: 93712,
      impressions: 393005
    },
    {
      ctr: 0.27251045096802823,
      keys: ['2021-09-10', 'https://mondo.rs/Sport/Tenis/a1528706/Novak-Djokovic-zamalo-diskvalifikovan-sa-US-opena.html'],
      clicks: 93349,
      impressions: 342552
    },
    {
      ctr: 0.22042795846910482,
      keys: ['2021-11-27', 'https://mondo.rs/Info/Drustvo/a1561695/Za-4-godine-ustedeo-7000-evra-na-grejanju.html'],
      clicks: 87129,
      impressions: 395272
    }
  ]
}

问题原因 #

Google Apps Script的setValues()方法要求传入二维数组，结构为[[行1列1值, 行1列2值, 行1列3值], [行2列1值, 行2列2值, 行2列3值]]，每个子数组对应表格的一行。你当前的写法是把所有字段值都push到了一维数组里，所以会被识别为单行的多列值，全部挤在同一行。

修正后的完整代码 #

function dataFillTest(sheet, json, regex) {
  sheet.clear();
  sheet.appendRow(["Date", "Link", "Clicks", "Category"]);

  var data = [];
  // 循环构造二维数组，每个子数组对应一行数据
  for (var i = 0; i < 5; i++) {
      data.push([
        json.rows[i].keys[0], 
        json.rows[i].keys[1], 
        json.rows[i].clicks
      ]);
  }
  Logger.log("Contents of data after for loop");
  Logger.log(data);
  
  var lastRow = sheet.getLastRow();
  // 调整range参数匹配实际数据的行列数
  sheet.getRange(lastRow + 1, 1, data.length, 3).setValues(data);

  var cell = sheet.getRange("D2:D");
  cell.setFormula('=REGEXEXTRACT(B2; "https://'+ regex+ '(\\w+)")');
}

修改点说明 #

• 循环中每次push的是包含三个字段的子数组，最终得到符合setValues要求的二维数组
• getRange的第三个参数（行高度）修改为data.length，和实际数据行数一致，第四个参数（列宽度）修改为3，匹配每行的字段数
• 直接将data传入setValues，不需要额外套一层数组

内容的提问来源于stack exchange，提问作者Gilbert