如何在Google SpreadSheets上动态引用多个工作表？

I use a Google SpreadSheet add-on to make Google Analytics Reports. My problem is that for each query this add-on creates a different sheet. I often have around 50 different sheet with data that must be compiled into a single sheet. For example:

我使用Google SpreadSheet插件制作Google Analytics报告。我的问题是,对于每个查询,此加载项会创建一个不同的工作表。我经常有大约50张不同的数据表,必须将数据编译成一张。例如:

I might have 3 sheets named: "Organic Data", "Direct Data" and "Other Data"

我可能有3张名为“有机数据”,“直接数据”和“其他数据”

Each sheet has a different set of data that I want to compile into a single table in a 4th sheet called "Report". This table will have in its first column data from the "Organic Data" sheet, in its second column data from the "Direct Data" sheet and in its third column data from the "Other Data" sheet.

每张工作表都有一组不同的数据,我想在第四张表中编译成一个名为“Report”的表。该表的第一列数据来自“有机数据”表,第二列数据来自“直接数据”表,第三列数据来自“其他数据”表。

The way I´m doing this is by referencing the cells one by one. So in the "Report" sheet I will write =Organic Data!B4 and then drag to fill the other cells of the column, and then I would do this for the other sheets to fill up the other columns of the "Report" sheet.

我这样做的方法是逐个引用单元格。因此,在“报告”表中,我将写入= Organic Data!B4,然后拖动以填充列的其他单元格,然后我将对其他工作表执行此操作以填充“报表”工作表的其他列。

The problem is that, as I´ve said, I often have more than 50 different sheets and referencing them can get quite hard.

问题是,正如我所说,我经常有超过50张不同的纸张,并且引用它们会变得相当困难。

I would like to do this dynamically. Have the names of the sheets on a cell and use that to reference the cells. For example:

我想动态地这样做。在单元格上有工作表的名称,并使用它来引用单元格。例如:

In the "Report" sheet:

在“报告”表格中:

A1 = "Organic Data", B1 = "Direct Data", C1 = "Other Data"

A1 =“有机数据”,B1 =“直接数据”,C1 =“其他数据”

In cell "Report!A2" I´ve tried writing =CONCAT("=",A1,"!","B4") hoping to get in this cell the value of the cell "Organic Data!B4", but instead I get the string "=Organic Data!B4, and not the value of the cell in "Organic Data" sheet.

在单元格“Report!A2”中我试过写= CONCAT(“=”,A1,“!”,“B4”)希望在这个单元格中获取单元格“Organic Data!B4”的值,而是我得到字符串“=有机数据!B4,而不是”有机数据“表中单元格的值。

I`ve made a custom formula to solve this problem:

我已经制定了一个自定义公式来解决这个问题:

function referenceSheet(sheetName, cellInput) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ""
  var cell = sheetName+'!'+cellInput;

  var value = ss.getRange(cell).getValue();

  return value;
}

It works quite well. In my example above to get the value of the cell "Organic Data!B4" into the cell "Report!A2", I need to write on "Report!A2" the following:

它运作得很好。在上面的示例中,为了将单元格“Organic Data!B4”的值放入单元格“Report!A2”中,我需要在“Report!A2”上写下以下内容:

"=referenceSheet(A1, cell("address",B4))"

and it will return the value of the cell "Organic Data!B4".

它将返回单元格“Organic Data!B4”的值。

I do have some issues with this custom formula that I´ve made. Mainly, custom formulas don´t refresh onChange, so if I change the value of "Organic Data!B4", "Report!A2" will not change.

我对这个定制公式有一些问题。主要是,自定义公式不刷新onChange,所以如果我更改“Organic Data!B4”的值,“Report!A2”将不会更改。

I would like to know if there´s a native formula that does something similar to my custom formula?

我想知道是否有一个与我的自定义公式类似的原生公式?

If not, what´s the best way to make my custom formula to refresh as I change the date from other cells?

如果没有,当我从其他单元格更改日期时,最好的方法是使我的自定义公式刷新?

Thank You Very Much!

非常感谢你!

1 个解决方案

#1

I´ve posted the same question on Google Forums and got a nice answer.

我在Google论坛上发布了相同的问题并得到了一个很好的答案。

There is a native formula call =INDIRECT

有一个原生公式call = INDIRECT

For my exemple it works like this:

对于我的例子,它的工作原理如下:

=INDIRECT(CONCATENATE(A$1,"!",cell("address",$B4)),true)

#1