1

Some sheets in my spreadsheet have data filled by the Query formula.

In a sheet I have data filled by a Query of another sheet filled by a Query itself.

This double Query makes that I can't read data usign GetValues() from a script.

When I try the simplest reading using:

var sheet = SpreadsheetApp.getActive().getSheetByName('Timeline');
var data = sheet.getRange('A15').getValue();
Logger.log(data);

the outcome is empty. In the cell A15 I have a query formula calling a sheet where data are filled by a Query, in turn.

Google script can't read values from a query of a query, I reckon.

Do you have any idea please?

Thanks.

soneangel
  • 621
  • 3
  • 10
  • 22
  • Does `A15` itself contain a value? I can't recreate this behaviour you mention. – Robin Gertenbach Dec 01 '15 at 11:18
  • `A15` contains a query formula. Exactly the following: `=IFERROR(QUERY(Dates!C3:J, "Select D,I,J,G where D!='' and toDate(I)> Date '31-12-2000' order by I,C"))` The fact is that the range `Dates!C3:J` is populated itself by a Query. I got the this is the error, but there is a solution to that? Thanks Robin. – soneangel Dec 01 '15 at 16:09
  • Did you check the value that is in A15? is it blank? Possibly due to the sorting. – Robin Gertenbach Dec 01 '15 at 16:14
  • No, I excluded blanks from the selection. I've checked and the error is due to the following statement in the query: `toDate(I)> Date '31-12-2000'` that I need because I want to exclude some row that in column `I` has by default '31-12-1899' How can I clean data in another way? – soneangel Dec 01 '15 at 17:00

2 Answers2

0

@Robin The problem was the where clause toDate(I)> Date '31-12-2000'.

I've replaced it with YEAR(I)>2000 and the script reads all cells now.

The reason I use the clause is because in my query I need to exclude rows with empty dates. Although they appear empty it seems they have some hidden value that I reckon is 31-12-1899. This is probably because the cell is formatted as Date. I'll investigate in it and this should also be another question that is not related with the current one.

soneangel
  • 621
  • 3
  • 10
  • 22
0

I think it is because dates are in timestamp. and your query probably consider your empty value as a 0, that correspond in timestamp, to the first time ( which is 1/1/1900 at 00h00, then, each increment represent a second.

You should try a blank detection with soething like:

var A= getCell(x,Y).getValue()
if (A=""){ 
....
}
LMS
  • 160
  • 3
  • 11