javascript - 如何比较两张工作表并删除/添加第 1 行中具有不同值的任何列？谷歌脚本

Question

我想比较两张表（基于第 1 行中的标题值）并删除具有唯一值的任何列（没有匹配项）。例如，假设 Sheet1, Row 1 data 和 Sheet 2, Row 1 是统一的，如果用户在任何工作表中添加/删除列，我希望始终将两个工作表中的列数与其值匹配

工作表标题的屏幕截图。

如果两张纸看起来像这样

并且用户添加了一个新列 N

或删除第 N 列

如何通过删除表 1 中的奇数/不同列来确保两张表匹配？

我已经尝试在下面修改此代码，但我不能只取出唯一的代码。此代码仅查找具有已定义值的标头。

function deleteAloneColumns(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastColumnPos = sheet.getLastColumn();
  var headers = sheet.getRange( 1 ,1, 1, lastColumnPos ).getValues()[0];
  for( var i = lastColumnPos ; i < 1; i--){
    if( headers[i] === "alone" ) sheet.deleteColumn(i);
  }
 SpreadsheetApp.getUi().alert( 'Job done!' );
}

任何帮助比较和删除具有唯一值的列将不胜感激。

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

问题

基于标题行值不匹配的资产负债表。

解决方案

如果我理解正确，您有一个运行验证的源表和两个主要用例：用户添加一个名称不同于任何其他列的新列（如果您想检查该列是否与 sheet1 中的列严格匹配，它易于修改）在源表中或删除应该存在的表。

const balanceSheets = (sourceShName = 'Sheet1',targetShName = 'Sheet2') => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const s1 = ss.getSheetByName(sourceShName);
  const s2 = ss.getSheetByName(targetShName);

  const s2lcol = s2.getLastColumn();

  //keep all vals from source to reduce I/O
  const s1DataVals = s1.getDataRange().getValues();

  const s2Vals = s2.getRange(1, 1, 1, s2lcol).getValues();

  const h1Vals = s1DataVals[0];
  const h2Vals = s2Vals[0];

  //assume s1 is source (validation) sheet
  //assume s2 is target sheet that a user can edit

  //case 1: target has value not present in source -> delete column in target
  let colIdx = 0;
  h2Vals.forEach(value => {
    const isOK = h1Vals.some(val => val===value);

    isOK ? colIdx++ : s2.deleteColumn(colIdx+1);
  });

  //case 2: target does not have values present in source -> append column from source
  h1Vals.forEach((value,index) => {
    const isOK = h2Vals.some(val => val===value);
    !isOK && s2.insertColumnAfter(index);

    const valuesToInsert = s1DataVals.map(row => [row[index]]);

    const numRowsToInsert = valuesToInsert.length;

    s2.getRange(1,index+1, numRowsToInsert,1).setValues(valuesToInsert);
  });

};

展示柜

这是一个关于它如何作为宏工作的小演示：

笔记

1. 用两个解决你的问题是次优的，但我将 I/O 的数量保持在较低水平（例如，可以通过移出循环而只跟踪列索引forEach来进一步降低它）。deleteColum
2. 该脚本使用 V8 提供的 ES6 功能，所以请小心（尽管我建议尽快迁移 - 即使遇到错误/不一致，它也比成本更值得。
3. UPD通过将工作表名称移动到参数列表使脚本更加灵活。
4. UPD2在讨论了deleteColumn()行为问题后，更新了答案以使列指针保持在界限内（对于那些对此感到好奇的人 -forEach不断增加index，同时deleteColumn减少任何给定索引的界限）。

参考

1. insertColumnAfter()方法参考