Querying data from a Google Spreadsheet that includes formulas

[Craig Pearce]

I have visualizations that pulls data from a Google Spreadsheet.

One of the columns has the following formula.

=ARRAYFORMULA(IF(G2:G= "Value X", 0,IF(G2:G="Value Y",1,IF(G2:G="Value Z",1))))

(arrayformula used to allow the calculation to expand as data is added).

When I have the Goog spreadsheet open, the visualization is produced properly (and a table view displays the correct values).  When it is closed, I get a timed out error (and the table displays empty cells for this column).

This continues if I modify the formula to;

=IF(G2:G= "Value X", 0,IF(G2:G="Value Y",1,IF(G2:G="Value Z",1)))

Questions/Comments

1. Is there a way to pull results of a formula from a google spreadsheet that isn't open?

2. I have seen some people suggest that you can copy the 'values only' into another column. This wouldn't work since the spreadsheet is constantly and automatically updated and therefore I would either have to manually do this X times/day since the other option of =G2:G is also a formula (and would run into the same problem).

Any help would be much appreciated.

Thanks

[Romain Vialard]

Pulling results of a formula from a google spreadsheet that isn't open was working not so long ago. It's a new behaviour in Google Sheet and I'm not sure if it's intended or if Google is working on a fix.

In the meantime, you could try and use Google Apps Script instead of formulas to copy the values in other columns, without formulas (using get and setValues()) 

[Craig Pearce]

Thanks for reviewing my question Romain.

I've done some research and came up with the following script/function.  However, it doesn't seem to work.  Any tips?

function myfunction(){

 var values = SpreadsheetApp.getActiveSheet().getRange("M2:M").getValues(); 

 var range = SpreadsheetApp.getActiveSheet().getRange("N2:N");

 range.setValues(values);

[Craig Pearce]

Never mind... I didn't realize the script runs in the document it was created in, not the document that is open.