查询(导入范围)插入所需的行以避免数据覆盖

Question

我想开发一个脚本，允许在 A 列中应用相同的函数，同时引用下面的所有行(从 A1 到 A600(，这是一个 Query(Importrange(，要插入的行数不是固定的(从 1 到 300(，因此必须在复制数据之前插入。

我首先在单元格 A1 中开发了一个公式，该公式运行良好，但这意味着在单元格中复制块 600 次以覆盖所有行：

`={Query(IMPORTRANGE('Master Table Projects'!T503,"Impacted Formula!A5:R1000"),"where Col1 is not Null");Query(IMPORTRANGE('Master Table Projects'!T467,"Impacted Formula!A5:R1000"),"where Col1 is not Null");Query(IMPORTRANGE('Master Table Projects'!T15,"Impacted Formula!A5:R1000"),"where Col1 is not Null")}

因此，我必须开发一个带有循环的脚本，以将此公式从 T1 应用到 T600。我试过这个：

function myFunction() {
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1);
for (r=1; r<100;r++) {
var sa=(Query(IMPORTRANGE('Master Table Projects'!Tr,"Impacted Formula!A5:R1000"),"where Col1 is not Null")); 
ss.getRange(r,1).setValue(sa); 
};

这不起作用，因为我收到一条警报消息： 参数列表(第 19 行，文件 "learnings.gs"(之后的语法错误：缺少(">

我根本不是编程方面的专家，但是： 我想我的"var sa"设置不正确，但我不知道如何纠正它。 Tr对我来说意味着"T1到T600"，但很可能写得不好。

我附上了我想得到的结果的图片： 预期成果

通过添加@ziganotschka提出的代码行

sheet.getRange("A" + sheet.getLastRow()).setValue(sa);

我得到了新图片中附加的结果，这是一个很好的改进，因为只显示导入的行，这些行有一些结果要显示，但是： 导入的数据仍然存在重叠的问题。最后一行(对应于 T999(必须手动删除才能看到一些结果。改进的宏

如果您单击电子表格的链接，我可以在之后为您提供访问权限，但由于我的公司限制，我无法共享公共链接(此选项被阻止(。

你能帮我纠正这些行以使其工作吗？

感谢@ziganotschka这里是工作代码：

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var limit = ss.getSheetByName('Master Table Projects').getRange("T2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
Logger.log(limit);
for (var r=2; r<=limit; r++) { 
var sa="=Query(IMPORTRANGE('Master Table Projects'!T"+r+',"Impacted Formula!A5:R1000"),"where Col1 is not Null")'; 
var firstEmptyRow = sheet.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()+1;
Logger.log(firstEmptyRow);
sheet.getRange("A" + firstEmptyRow).setValue(sa);
SpreadsheetApp.flush();
}
}

非常感谢 伊萨

Answer 1

前提是您有一个有效的公式，为了使用动态行索引为其分配，您需要正确连接它：

• 当您构建变量sa时，它可以由对其他变量和文本的引用组成
• 必须通过将 in=t 括在单引号或双引号中将文本指定为字符串
• 如果文本(公式(已经包含一些引号，请确保调整外部换行引号，以确保单引号('(和双引号("(或转义引号的交替 - 请参阅此处
• 对于公式，=应该是文本的一部分
• 若要协调文本部分和变量引用，请使用+符号
• 您的IMPORTRANGE公式可能会返回空行，每次设置新公式时都必须查询下一个空闲行。
• 对此有用的是方法getNextDataCell((

样本：

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var limit = ss.getSheetByName('Master Table Projects').getRange("T2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
for (var r=2; r<=limit; r++) { 
var sa="=Query(IMPORTRANGE('Master Table Projects'!T"+r+',"Impacted Formula!A5:R1000"),"where Col1 is not Null")'; 
var firstEmptyRow = sheet.getRange("A2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()+1;
sheet.getRange("A" + firstEmptyRow).setValue(sa);
SpreadsheetApp.flush();
}
}

笔记：

• 在每一行中使用setValue()并不是好的做法，因为将来考虑将请求存储在数组中，并在退出后使用 setValues(( 将所有值一并循环到电子表格for
• 仔细检查您的公式是否正确(IMPORTRANGE需要指向电子表格的 URL(

更新

• 若要确保公式不会相互覆盖，可以实现：sheet.getRange("A" + sheet.getLastRow()+1).setValue(sa);
• 但是，在您的情况下，导入范围会创建空行，尽管查询"其中 Col1 不为 Null"，但这些空行不是脚本。
• 因此，最好将所有查询与;连接起来，并将它们设置到同一个单元格中
• 如果要动态检索工作表"主表项目"中的最后一行 og 列 T，则可以实现

var limit = ss.getSheetByName('Master Table Projects').getRange("T2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

然后循环直到limit

样本：

function myFunction2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var sa="={";
var limit = ss.getSheetByName('Master Table Projects').getRange("T2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
for (var r=2; r<limit; r++) { 
var sa1="Query(IMPORTRANGE('Master Table Projects'!T"+r+',"Impacted Formula!A5:R1000"),"where Col1 is not Null")';
sa=sa+sa1+";";
}
sa = sa.slice(0,-1) +"}";
sheet.getRange("A2").setValue(sa);
}