天道酬勤,学无止境

importrange

解决下拉列表 importRange 问题的脚本解决方法(Script workaround to solve importRange problems for drop down list)

问题 我正在分享这个,希望我能解决我面临的这个问题。 我正在使用数据验证来获取下拉菜单的值。 这些值来自我称为“计算”的工作表中的 Range(“A1:A40”) 由于我有大约 50 个使用相同下拉值和相同模式的电子表格,因此我决定使用 importRange 函数来填充 Sheet(“Calculation”).Range(“A1:A50”) 中的信息。 importRange 从我专门用于此目的的单独电子表格中获取列表。 这样做的目的仅仅是因为这是我想要更新和更改的类别列表,我希望下拉菜单能够考虑这些更改,而不必手动更改 50 多个电子表格中的值。 现在理论上这将没有问题,但 importRange 函数是如此易怒,它显示了随机电子表格发生的 #REF 错误。 我看到帖子抱怨同样的问题,所以我放弃了 importRange 将是一个可行的解决方案的希望。 我希望您提出一个很好的解决方法,以避免出现此类问题。 我想到的一个解决方案是让脚本将值从一张工作表复制到所有这些工作表,并让这个脚本每天都在工作,但我不知道如何将整个范围从一个电子表格复制到另一个,坦率地说,我不确定这是否解决方案是最好的。 我使用跨电子表格脚本的经验是,从 50 多个工作表中的几个单元格获取数据的脚本可能需要 5 分钟以上,最终会因超出允许的时间而停止。 回答1 尝试添加一个菜单项,该菜单项将导致运行刷新功能

2021-07-10 00:43:22    分类:技术分享    google-apps-script   google-sheets   google-sheets-formula   importrange

Force IMPORTRANGE to update at certain intervals

I saw several complains about the delay of updating data through IMPORTRANGE in Google Sheets but I need the opposite and don't want the second sheet to get updated automatically, just update at the end of the day for example. The code is already like this: =IMPORTRANGE("The Key","The_Page!B:D")

2021-06-28 18:35:48    分类:问答    google-sheets   google-drive-api   google-sheets-formula   importrange

Script workaround to solve importRange problems for drop down list

I am sharing this with hopes I workaround solution to this problem I am facing. I am using data validation to get values of a drop down menu. The values come from Range(“A1:A40”) in a sheet I called “calculation” Since I have about 50 spreadsheets that use the same drop down values and same pattern, I simply decided to use an importRange function to fill the information in Sheet(“Calculation”).Range(“A1:A50”). The importRange was taking the list from a separate spreadsheet that I dedicated for that purpose. The purpose of doing that was simply because this is a list of categories that I want

2021-06-28 01:07:36    分类:问答    google-apps-script   google-sheets   google-sheets-formula   importrange

使用动态 IMPORTRANGE 填充 ArrayFormula(Fill ArrayFormula with dynamic IMPORTRANGE)

问题 我有一个 Google 表格,可以从各种表格中收集学生的分数以创建完整的分数表。 每位教师在他/她自己的电子表格中输入分数,并以纸张编号作为表格名称。 any 的每张纸只有两列: Roll#和Marks 。 使用纸张编号(纸张名称)、Google Sheets Key、 Roll#和Marks我手动创建公式以使用 Sort、ArrayFormula 和 IMPORTRANGE 函数整理数据。 这有效。 但是手动创建公式是一种容易出错的方法。 另外,我想教每位老师如何做到这一点。 我想要的是在单元格 G23 中添加公式,以便它根据其上方的单元格 G2:G22 中的条目动态创建 IMPORTRANGE。 如果有三个名称,则创建三个 IMPORTRANGE,如果有五个名称,则动态创建五个 IMPORTRANGE,以便这些 IMPORTRANGE 通过封装 ArrayFormula 进行合并和排序。 现有公式是我手动创建的,并且有效。 我想要的只是拥有公式,以便如果在 G2-G22 中添加新教师姓名,则在 ArrayFormula 中添加新的 IMPORTRANGE。 回答1 由于 importrange 函数具有输出数组的能力,因此无法在数组中进行迭代。 要合并您的公式,请使用以下公式: ="=sort(ARRAYFORMULA({"&JOIN(";",ArrayFormula(

2021-06-23 23:13:38    分类:技术分享    google-sheets   google-sheets-formula   array-formulas   importrange

使用 IMPORTRANGE 将多个电子表格合二为一(Combining multiple spreadsheets in one using IMPORTRANGE)

问题 我想将多个电子表格的数据聚合到一个电子表格中。 电子表格 1 有一行字符串 A2:A500 电子表格 2 有一行字符串 A2:A500 电子表格 3 应该有一行 (Spreadsheet1!A2:A500 AND Spreadsheet2!A2:A500)。 不得以不同方式处理重复项。 我希望它们出现的频率与它们出现在不同工作表中的频率一样。 是否可以在不编写脚本或使用 jQuery(例如使用 IMPORTRANGE)的情况下做到这一点? 什么不起作用:我尝试使用 IMPORTRANGE 如下: ARRAY{IMPORTRANGE("key-of-spreadsheet1","list!A2:A500"), IMPORTRANGE("key-of-spreadsheet2", "list!A2:A500")} 这会导致错误。 回答1 您应该能够在电子表格 3 中使用垂直数组: ={IMPORTRANGE("Sheet1Key","SheetName!A2:A500");IMPORTRANGE("Sheet2Key","SheetName!A2:A500")} 回答2 当然,也可以将多个IMPORTRANGE()函数与QUERY()函数结合使用,这使我们可以更好地控制导入的结果。 例如,我们可以使用这样的结构: =QUERY( { IMPORTRANGE("key-or-url

2021-06-22 09:09:50    分类:技术分享    google-sheets   google-sheets-formula   array-formulas   importrange

How to allow access for importrange function via apps script?

When you enter an importrange function manually into a spreadsheet you receive a pop up and must 'allow access'. However, I'm trying to find a way to do this via a script because I'm creating many spreadsheets, each with a query-importrange function (I 'own' the spreadsheet which has data to import). There's too many for me to manually 'allow access' via the pop up and update the function to include the query function. Hence, I'm looking for a function call in apps script that can perform the same action that the pop up did. Code segment example below. Does anyone know of a function that can

2021-06-13 08:51:47    分类:问答    javascript   google-apps-script   google-sheets   importrange

IMPORTRANGE with condition

Using Google Sheets I want, within the same document, to import data from one sheet to another using IMPORTRANGE with conditions. I have tried unsuccessfully: =IF(IMPORTRANGE("https:URL","Inc Database!B2:B300")="permanent",IMPORTRANGE("htps://URL","Inc Database!A2:A300"),"") and =QUERY(IMPORTRANGE("https:/URL", "Inc Database!A2:A300"),"SELECT Col1 WHERE Col1 <> 'permanent'") and =FILTER(IMPORTRANGE("URL","Inc Database!A1:A250"),IMPORTRANGE("URL","Inc Database!B1:B250"="venture permanent")) I want the function to say: Import any values from range A that meet criterion "permanent" in range B. A

2021-06-03 20:38:18    分类:问答    google-sheets   google-sheets-formula   google-sheets-query   importrange

Combining multiple spreadsheets in one using IMPORTRANGE

I would like to aggregate the data of multiple spreadsheets into one spreadsheet. Spreadsheet 1 has a Row of Strings A2:A500 Spreadsheet 2 has a Row of Strings A2:A500 Spreadsheet 3 is supposed to have a Row of both (Spreadsheet1!A2:A500 AND Spreadsheet2!A2:A500). Duplicates shall not be handled differently. I would like them to appear as often as they appear in the different sheets. Is it possible to do this without writing a script or using jQuery, e.g. by using IMPORTRANGE? What does not work: I have tried using IMPORTRANGE as follows: ARRAY{IMPORTRANGE("key-of-spreadsheet1","list!A2:A500")

2021-05-25 01:15:50    分类:问答    google-sheets   google-sheets-formula   array-formulas   importrange

Fill ArrayFormula with dynamic IMPORTRANGE

I have a Google Sheets that collects marks of students from various sheets to create a complete mark sheet. Each teacher enters marks in his/her own spreadsheet with paper number as sheet names. Each sheet of any has just two columns: Roll# and Marks. Using combination of paper number (sheet name), Google Sheets Key, Roll# and Marks I MANUALLY create formulas to collate data using Sort, ArrayFormula and IMPORTRANGE functions. This works. But manually creating formulas is an error prone approach. Plus, I want to teach each teacher how to do this. What I want is to add formula in cell G23 so

2021-05-14 16:03:59    分类:问答    google-sheets   google-sheets-formula   array-formulas   importrange

Convert a string to a formula in Google Spreadsheet

I´d been more than 10 hours reading and trying different options with no success. I have this string (this is actually a string that is generated by other formulas) QUERY({IMPORTRANGE(A1;$D$1);IMPORTRANGE(A2;$D$1);IMPORTRANGE(A3;$D$1);IMPORTRANGE(A4;$D$1)};"select Col13, sum(Col1), sum(Col2), sum(Col3), sum(Col4), sum(Col5), sum(Col6), sum(Col7), sum(Col8), sum(Col9), sum(Col10), sum(Col11), sum(Col12) group by Col13";0) And I want it to be read as a formula. So for example I try this function: function doConvert(formula) { // Strip leading "=" if there if (formula.charAt(0) === '=') formula =

2021-05-08 13:05:23    分类:问答    google-apps-script   google-sheets   google-sheets-formula   google-sheets-query   importrange