how to reproduce an action on all the members of a list to write an "importrange" command from multiple http links

I'am trying to import a lot of Googlesheet into 1 using the "=importrange" command and a script.
here is so far where I'am getting stuck :

I have been able to do the code to import 1 sheet, but when it comes to import and deal with 2 or more sheets, I'am no longer able to "iterate" my actions.

here is an example with 3 http links of what I want to write into the box :

=QUERY({importrange("https://docs.google.com/spreadsheets/d/1ZvgM-rTpHJbBJB-F6iqtSE7NB5Jdl-_Q7LQL_ELhLo8?authuser=design%40wearemakers.fr&usp=drive_fs","'TOTAUX'!A3:C");importrange("https://docs.google.com/spreadsheets/d/1SH9iY55UEm9glHXfgFD2bpMCcDaQMChS8nsH_0SJ6RA?authuser=design%40wearemakers.fr&usp=drive_fs","'TOTAUX'!A3:C");https://docs.google.com/spreadsheets/d/1SH9iY55UEm9glHXfgFD2bpMCcDaQMChS8nsH_0SJ6RA/edit?usp=drivesdk","'TOTAUX'!A3:C")}, "Select * WHERE Col2 IS NOT NULL")

here is what the code produce actually :

=QUERY({importrange("https://docs.google.com/spreadsheets/d/174wJ6l1ohnYKxaKEQLjOiCprc1bwqlY0dougaLZqOs4/edit?usp=drivesdk,https://docs.google.com/spreadsheets/d/1ZvgM-rTpHJbBJB-F6iqtSE7NB5Jdl-_Q7LQL_ELhLo8/edit?usp=drivesdk,https://docs.google.com/spreadsheets/d/1SH9iY55UEm9glHXfgFD2bpMCcDaQMChS8nsH_0SJ6RA/edit?usp=drivesdk,,,,,,,,,","'TOTAUX'!A3:C")}, "Select * WHERE Col2 IS NOT NULL")

And here the code :

  var COLsheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("COLLECTEUR");
  var link = COLsheet.getRange(2,9,COLsheet.getMaxRows()).getValues();
  var plage = "'TOTAUX'!A3:C";
  var imp  = 'importrange("' + (link)+ '";"' + (plage) + '")';
  var que = '=QUERY({' + (imp) + '}, "Select * WHERE Col2 IS NOT NULL")';


/**
  for (var i in link) {
  var links = link[i];
}**/


  Logger.log(link)
  //Logger.log(links)

  COLsheet.getRange('D5').setValues([[que]]);

} ```


I have try to do something with[i], but I just don't really know what to do...

</details>


# 答案1
**得分**: 2

虽然我不确定我是否能正确理解您期望的结果，不过以下修改如何？

### 修改后的脚本：

```javascript
function myFunction() {
  var COLsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("COLLECTEUR");
  var link = COLsheet.getRange(2, 9, COLsheet.getLastRow() - 1).getDisplayValues().filter(([i]) => i);
  var plage = "'TOTAUX'!A3:C";
  var imp = link.map(([i]) => `importrange("${i}";"${plage}")`);
  var que = `=QUERY({${imp.join(";")}},"Select * WHERE Col2 IS NOT NULL")`;
  COLsheet.getRange('D5').setFormula(que);
}

• 当运行此脚本时，如果link的值是有效的电子表格URL，que将变为以下内容。

  =QUERY({
  importrange("https://docs.google.com/spreadsheets/d/###/edit";"'TOTAUX'!A3:C");
  importrange("https://docs.google.com/spreadsheets/d/###/edit";"'TOTAUX'!A3:C");
  importrange("https://docs.google.com/spreadsheets/d/###/edit";"'TOTAUX'!A3:C");
  ;
  ;
  ;
  },"Select * WHERE Col2 IS NOT NULL")

• 在var link = COLsheet.getRange(2,9,COLsheet.getMaxRows()).getValues()的情况下，将从所有行检索值。因此，我修改为var link = COLsheet.getRange(2, 9, COLsheet.getLastRow()).getValues().filter(([i]) => i)。

• 在此修改中，使用了模板文字。

• 为了创建importrange的公式，我使用了map。

参考：

• 模板文字
• map()
• setFormula(formula)

function myFunction() {
  var COLsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("COLLECTEUR");
  var link = COLsheet.getRange(2, 9, COLsheet.getLastRow() - 1).getDisplayValues().filter(([i]) => i);
  var plage = "'TOTAUX'!A3:C";
  var imp = link.map(([i]) => `importrange("${i}";"${plage}")`);
  var que = `=QUERY({${imp.join(";")}},"Select * WHERE Col2 IS NOT NULL")`;
  COLsheet.getRange('D5').setFormula(que);
}