Google Apps脚本有助于在Google电子表格中嵌入Google电子表格时出现错误？ [英] Google Apps Script To Help Error When Embedding Google Spreadsheet in a Google Site?

问题描述

电子表格A是欢迎新移民到我们的小镇的组的主数据源。它有很多非常敏感的数据，不能公开，甚至没有一点点。 （我们讨论的是孩子的姓名和出生日期以及他们上学的时间......保持电子表格安全是关键任务。）

因此，Spreadsheet B使用一个importRange和一个查询从电子表格A中提取必要的列以填充我们的会员目录。

然后，我将Spreadsheet B嵌入到组的Google Site中。 （如果你喜欢，可以到www.SewickleyNewcomers.com网站查看）

它的效果非常好。

但是。 。 。在我关闭Spreadsheets A&如果我尝试打开嵌入了会员目录的网页，嵌入的表格会显示#VALUE！。

当然后，我再次打开电子表格B，它说同样的事情：#VALUE！。

直到，我让电子表格B坐了几分钟。然后，在坐了几分钟之后，突然间，所有的数据都会重新填充。

如果我回到嵌入了会员目录的网页在这一点上，一切都很好。

据推测，问题是如果Spreadsheet B没有打开，它就无法从Spreadsheet A中提取数据。

但是我无法嵌入电子表格A，因为上面提到的列和表格不能公开访问。

有些详情：

• 电子表格A和电子表格B目前均设置为有链接的任何人作为分享偏好。 （尽管我最好喜欢将电子表格A恢复为私人。）




• 电子表格B嵌入Google网站，内置Insert Spreadsheet Widget。




• 以下是我目前使用的将电子表格A中的数据提取到电子表格B的公式：

  
  
  

  = arrayformula（query（importrange（ _ ，Master！A：Z），select Col3，Col2，Col22，Col18，Col19，Col20，Col21，Col24 where Col26 ='是的，请继续，并将我的联系信息包含在在线目录和打印目录中。'（Col4 ='Yes'或Col4 ='yes'）按Col3排序））

有人对这里发生的事情有任何想法吗？另外，我可以从Spreadsheet A和网站上获得必要的数据（我当然不需要通过Spreadsheet B，我只是想不出其他方式来保存Spreadsheet A上的所有其他数据私人的）？有没有一种解决方案在Google网站上使用某种Apps脚本来终结需要嵌入电子表格？

预先感谢！所有移动到Sewickley的人也都非常感谢你！

解决方案

importRange显示预计显示85％时间（根据我的实验）。
在其他15％期间，它显示＃N / A或#VALUE。

我相信如果在网页上找到#VALUE，只需等待很长时间（10-15分钟），即使您不重新打开电子表格，正确的数据也会返回到网页。

无论如何，要为避免此问题，您可以在主扩展程序A，
中创建一个工作表publicSheet，并使用公式从其他工作表中复制必要事项
，并通过文件>发布将该特定工作表发布到网络> publicSheet。
如果这样做，仍然没有人可以访问电子表格A，
的其他工作表，但数据永远不会从已发布的工作表中消失。

Spreadsheet A is the master data source for the group that welcomes Newcomers to our little town. It has a ton of very sensitive data in it and cannot be public, not even a little bit. (We're talking about names and birth dates of kids and where they go to school . . . keeping Spreadsheet A secure is mission critical.)

So, Spreadsheet B uses an importRange and a query to pull the necessary columns from Spreadsheet A to populate our "Member Directory".

Then, I embedded Spreadsheet B in the group's Google Site. (Feel free to check out the site if you like at www.SewickleyNewcomers.com )

And it works beautifully.

BUT . . . after I shut Spreadsheets A & B down and a few hours go by, if I try to open the webpage that has the Member Directory embedded in it, the embedded sheet says "#VALUE!"

When I then go and open Spreadsheet B again, it says the same thing: "#VALUE!".

UNTIL, I let Spreadsheet B sit for a few minutes. Then, after it's sat for a few minutes, all of a sudden, all of the data populates again.

AND if I go back to the webpage with the Member Directory embedded in it, all's good.

Presumably, the problem is that if Spreadsheet B is not open, it can't pull the data from Spreadsheet A.

But I can't embed Spreadsheet A because there are columns and sheets that cannot be publicly accessible, as mentioned above.

Some details:

• Both Spreadsheet A and Spreadsheet B are currently set to "Anyone with the link" as the sharing preference. (Although I'd ideally like to get Spreadsheet A back to "Private".)

• Spreadsheet B is embedded in the Google Site with the built-in Insert Spreadsheet Widget.

• Here's the formula I'm currently using to pull the data from Spreadsheet A to Spreadsheet B:

  =arrayformula(query(importrange("_","Master!A:Z"), "select Col3, Col2, Col22, Col18, Col19, Col20, Col21, Col24 where Col26='Yes, go ahead and include my contact information in the online directory and in the print directory.' and (Col4='Yes' or Col4='yes') order by Col3"))

Anybody have any thoughts about what's going on here? And ideas for other ways I can get the necessary data out of Spreadsheet A and onto the site (I certainly don't have to go through Spreadsheet B, I just couldn't think of another way to keep all the other data on Spreadsheet A private)? Is there a solution that uses some kind of Apps Script on the Google Site to end-run needing to embed the spreadsheet?

Thanks in advance! And all the people who move to Sewickley thank you, too!

解决方案

importRange shows what it is expected to show 85% of the time (according to my experiments). During the other 15%, it shows #N/A or #VALUE.

I believe that if, having found #VALUE on the web page, you just wait a lot (10-15 minutes), then the correct data will come back to the web page, even if you do not reopen the spreadsheets.

Anyway, to avoid this problem, you can make a worksheet "publicSheet" within the master spreadhseet A, fill it with formulas that copy necessary things from the other worksheets, and publish this particular worksheet by File>Publish to the web>"publicSheet". If you do so, still nobody can access the other worksheets of the spreadsheet A, but the data will never disappear from the published worksheet.

这篇关于Google Apps脚本有助于在Google电子表格中嵌入Google电子表格时出现错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！