title: Google Apps Script To Help Error When Embedding Google Spreadsheet in a Google Site?
body: <p>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.)</p>
  
  <p>So, Spreadsheet B uses an importRange and a query to pull the necessary columns from Spreadsheet A to populate our "Member Directory".</p>
  
  <p>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 )</p>
  
  <p>And it works beautifully.</p>
  
  <p>BUT . . . after I shut Spreadsheets A &amp; 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!"</p>
  
  <p>When I then go and open Spreadsheet B again, it says the same thing: "#VALUE!". </p>
  
  <p>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.</p>
  
  <p>AND if I go back to the webpage with the Member Directory embedded in it, all's good.</p>
  
  <p>Presumably, the problem is that if Spreadsheet B is not open, it can't pull the data from Spreadsheet A.</p>
  
  <p>But I can't embed Spreadsheet A because there are columns and sheets that cannot be publicly accessible, as mentioned above.</p>
  
  <p>Some details:</p>
  
  <ul>
  <li><p>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".)</p></li>
  <li><p>Spreadsheet B is embedded in the Google Site with the built-in Insert Spreadsheet Widget.</p></li>
  <li><p>Here's the formula I'm currently using to pull the data from Spreadsheet A to Spreadsheet B:</p>
  
  <p>=arrayformula(query(importrange("<em>_</em>","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"))</p></li>
  </ul>
  
  <p>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?</p>
  
  <p>Thanks in advance! And all the people who move to Sewickley thank you, too!</p>