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

huangapple
go评论109阅读模式
英文:

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

问题

I have translated the provided text. Here is the translated content:

  1. 我正试图使用“=importrange”命令和脚本将许多Google表格导入到一个表格中。
  2. 到目前为止，这是我卡住的地方：
  4. 我已经能够编写代码以导入一个表格，但是当涉及导入和处理2个或更多表格时，我不再能够“迭代”我的操作。
  6. 以下是一个示例，其中包含3个要写入框中的http链接：
  8. =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")
  10. 以下是代码目前生成的内容：
  12. =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")
  14. 以下是代码：
  16. function IMPORT_002() {
  17.   var COLsheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("COLLECTEUR");
  18.   var link = COLsheet.getRange(2,9,COLsheet.getMaxRows()).getValues();
  19.   var plage = "'TOTAUX'!A3:C";
  20.   var imp  = 'importrange("' + (link)+ '","' + (plage) + '")';
  21.   var que = '=QUERY({' + (imp) + '}, "Select * WHERE Col2 IS NOT NULL")';
  23.   Logger.log(link)
  24.   COLsheet.getRange('D5').setValues([[que]]);
  25. }

我已提供翻译的文本，请告诉我如果您需要进一步的帮助。

英文:

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 :

  1. =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 :

  1. =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 :

  1.   var COLsheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("COLLECTEUR");
  2.   var link = COLsheet.getRange(2,9,COLsheet.getMaxRows()).getValues();
  3.   var plage = "'TOTAUX'!A3:C";
  4.   var imp  = 'importrange("' + (link)+ '";"' + (plage) + '")';
  5.   var que = '=QUERY({' + (imp) + '}, "Select * WHERE Col2 IS NOT NULL")';
  8. /**
  9.   for (var i in link) {
  10.   var links = link[i];
  11. }**/
  14.   Logger.log(link)
  15.   //Logger.log(links)
  17.   COLsheet.getRange('D5').setValues([[que]]);
  19. } ```
  22. I have try to do something with[i], but I just don't really know what to do...
  24. </details>
  27. # 答案1
  28. **得分**: 2
  30. 虽然我不确定我是否能正确理解您期望的结果，不过以下修改如何？
  32. ### 修改后的脚本：
  34. ```javascript
  35. function myFunction() {
  36.   var COLsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("COLLECTEUR");
  37.   var link = COLsheet.getRange(2, 9, COLsheet.getLastRow() - 1).getDisplayValues().filter(([i]) => i);
  38.   var plage = "'TOTAUX'!A3:C";
  39.   var imp = link.map(([i]) => `importrange("${i}";"${plage}")`);
  40.   var que = `=QUERY({${imp.join(";")}},"Select * WHERE Col2 IS NOT NULL")`;
  41.   COLsheet.getRange('D5').setFormula(que);
  42. }

  • 当运行此脚本时，如果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。

参考：

英文:

Although I'm not sure whether I could correctly understand your expected result, how about the following modification?

Modified script:

  1. function myFunction() {
  2.   var COLsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(&quot;COLLECTEUR&quot;);
  3.   var link = COLsheet.getRange(2, 9, COLsheet.getLastRow() - 1).getDisplayValues().filter(([i]) =&gt; i);
  4.   var plage = &quot;&#39;TOTAUX&#39;!A3:C&quot;;
  5.   var imp = link.map(([i]) =&gt; `importrange(&quot;${i}&quot;;&quot;${plage}&quot;)`);
  6.   var que = `=QUERY({${imp.join(&quot;;&quot;)}},&quot;Select * WHERE Col2 IS NOT NULL&quot;)`;
  7.   COLsheet.getRange(&#39;D5&#39;).setFormula(que);
  8. }

  • When this script is run, when the values of link are the valid Spreadsheet URLs, que becomes as follows.

    1.   =QUERY({
    2.     importrange(&quot;https://docs.google.com/spreadsheets/d/###/edit&quot;,&quot;&#39;TOTAUX&#39;!A3:C&quot;);
    3.     importrange(&quot;https://docs.google.com/spreadsheets/d/###/edit&quot;,&quot;&#39;TOTAUX&#39;!A3:C&quot;);
    4.     importrange(&quot;https://docs.google.com/spreadsheets/d/###/edit&quot;,&quot;&#39;TOTAUX&#39;!A3:C&quot;);
    5.     ;
    6.     ;
    7.     ;
    8.   },&quot;Select * WHERE Col2 IS NOT NULL&quot;)

  • In the case of var link = COLsheet.getRange(2,9,COLsheet.getMaxRows()).getValues(), the values are retrieved from all rows. So, I modified to var link = COLsheet.getRange(2, 9, COLsheet.getLastRow()).getValues().filter(([i]) =&gt; i).

  • In this modification, the template literal was used.

  • In order to create the formula of importrange, I used map.

References:

huangapple
  • 本文由 发表于 2023年5月10日 17:47:24
  • 转载请务必保留本文链接：https://go.coder-hub.com/76216998.html
  • google-apps-script
  • google-sheets
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定