google-apps-script - 谷歌工作表脚本不断追加空白列

Question

我有以下 google sheet 脚本，它调用 URL 来获取数据并用数据填充工作表。该 URL 仅包含“名称”和“用户名”两列，但是当我出于某种原因运行脚本时，它会在每条记录的开头添加 3 个空白列，并在每条记录的末尾添加某种唯一键列。脚本如下，我似乎无法弄清楚它为什么这样做：

function populateSheetWithCSV(sheet, csvUrl, user, pw) {
  var resp = UrlFetchApp.fetch(csvUrl, {
    headers: {
      // use basic auth
      'Authorization': 'Basic ' + Utilities.base64Encode(
      user + ':' + pw, Utilities.Charset.UTF_8)
    }
  });
  var doc = XmlService.parse(resp);
  var users = doc.getRootElement().getAllContent();
  var spreadsheetContent = [];
  var maxLength = 0;
  var headerRow = ['Name', 'Username'];
  for (var j=0; j<users.length-1; j++)
  {
   var length = users[j].getValue().trim().split("\n").length;
   if (maxLength < length)
   {
     maxLength = length;
   }
  }
  // Get all the data into a 2D Array
  for (var i=0; i<users.length-1; i++)
  {
    var userDetails = users[i].getValue().trim().split("\n");
    if (userDetails.length > 1) {
      if (userDetails.length < maxLength)
      {
        var diff = maxLength - userDetails.length;
        for (var k = 0; k<diff; k++)
        {
          userDetails.unshift("");
        }
      }
      spreadsheetContent.push(userDetails);
    } 
  }
  Logger.log(spreadsheetContent);

// clear everything in the sheet
    sheet.clearContents().clearFormats();
// set headers
sheet.appendRow(headerRow);
// set the values in the sheet (as efficiently as we know how)
    sheet.getRange(
        2, 1,
        spreadsheetContent.length /* rows */,
        maxLength /* columns */).setValues(spreadsheetContent);
}

*** 更新 ***

我发现我从中获取数据的 URL 以 XML 格式返回数据，结果如下所示。有没有办法修改脚本以使用 XML 数据？

<?xml version="1.0" encoding="utf-8"?>
<NewDataSet>
  <Table>
    <Name>First Name 1</Name>
    <Username>user_name1</Username>
    <UniqueIdentifier>34644cba5-4090-4e3d646ba-222a6fe549fc</UniqueIdentifier>
  </Table>
  <Table>
    <Name>First Name 2</Name>
    <Username>user_name2</Username>
    <UniqueIdentifier>34644cba5-4090-4e3d646ba-222a6fe549fc</UniqueIdentifier>
  </Table>
</NewDataSet>

标签： google-apps-scriptgoogle-sheets

Answer 1

可能是CSV的问题。

代码的最小工作片段（没有您的 CSV）没有显示问题：

function populateSheetWithCSV_TEST() {

    var headerRow = ['Name', 'Username'];
    var sheet = SpreadsheetApp.getActiveSheet();
    var spreadsheetContent = [['name1', 'user1'], ['name2', 'user2']];
    var maxLength = spreadsheetContent[0].length;

    // clear everything in the sheet
    sheet.clearContents().clearFormats();
    // set headers
    sheet.appendRow(headerRow);
    // set the values in the sheet (as efficiently as we know how)
    sheet.getRange(
        2, 1,
        spreadsheetContent.length /* rows */ ,
        maxLength /* columns */ ).setValues(spreadsheetContent);
}

输出：

---

更新

您可以通过这种方式将 XML 转换为数组：

function parse() {

  xml = `<?xml version="1.0" encoding="utf-8"?>
  <NewDataSet>
    <Table>
      <Name>First Name 1</Name>
      <Username>user_name1</Username>
      <UniqueIdentifier>34644cba5-4090-4e3d646ba-222a6fe549fc</UniqueIdentifier>
    </Table>
    <Table>
      <Name>First Name 2</Name>
      <Username>user_name2</Username>
      <UniqueIdentifier>34644cba5-4090-4e3d646ba-222a6fe549fc</UniqueIdentifier>
    </Table>
  </NewDataSet>`

  const getValues = node => 
    ['Name','Username'].map(name => node.getChild(name).getText());
  
  const rows = XmlService.parse(xml).getRootElement()
    .getChildren('Table').map(getValues);

  Logger.log(rows); // out: [[First Name 1, user_name1], [First Name 2, user_name2]]

  /* old school
  var data   = XmlService.parse(xml);
  var tables = data.getRootElement().getChildren('Table');
  var rows   = [];
  for (table of tables) {
    var name = table.getChild('Name').getText();
    var username = table.getChild('Username').getText();
    rows.push([name, username])
  }  
  */

}

Bun 我对 XML 解析几乎一无所知。也许我的解决方案不是最好的。

---

更新 2

rows您可以像在脚本中那样将数组添加到表中：

...
var spreadsheetContent = rows;

// clear everything in the sheet
sheet.clearContents().clearFormats();

// set headers
sheet.appendRow(headerRow);

// set the values in the sheet (as efficiently as we know how)
sheet.getRange( 
  2, 1,
  spreadsheetContent.length /* rows */,
  maxLength /* columns */).setValues(spreadsheetContent);
}

我没有测试过。但如果它已经工作了，它现在应该工作了。