Query Language Reference: request by dates

Question

I do gave a google spreadsheet №1 and spreadsheet №2. I need get data from spreadsheet №1 and filter out it and then put in spreadsheet №2. I do it without Google Scripts with help function Query.

Formula of query:

= query( main_data_sheet ;"select B, D, H, count(I) 
where  (O is not null and O > date '"&TEXT($B$5;"yyyy-mm-dd")&"' 
and O < date '"&TEXT($B$6;"yyyy-mm-dd") &"') and (U = '"&$A$3&"') and 
(R = '"&$B$8&"' or R = '"&$B$9&"' or R = '"&$B$10&"' or R = '"&$B$11&"' or R = '"&$B$12&"')
group by B,D,H  label B '', D '', H '', count(I) ''  ";0)

and this is work great. But i need to build this request by Google Scripts.

So i tried this function So my Code is

function test_gvizQuery() {      
  var result = Utils.gvizQuery( "ID_spreadsheet", "SELECT A,B,O WHERE O > date '01-01-2019' format O 'dd-MMM-yyyy' " , "РЕЕСТР2", 0);

  Logger.log("result = " + result);
  var sheet_2 =  SpreadsheetApp.getActive().getSheetByName("Using_the_Query_Language"); 
  sheet_2.getRange(1, 1, 1987, 3).setValues(result);      
}

About using this function

Just drop that module into its own file in your GAS editor then you can call it as follows:

// result is an array of objects if header row is specified, otherwise it is an array of arrays
var result = Utils.gvizQuery(
    "[YOUR_SPREADSHEET_ID]", 
    "[YOUR_QUERY_STRING]", 
    [SHEET_ID_IF_NEEDED], // can be a number (the sheetId), or the name of the sheet; if not needed, but headers are, pass in undefined
    [HEADER_ROW_INDEX_IF_NEEDED] // always a number
);

so i did the same. when i use

"SELECT A,B,O WHERE O > date '01-01-2019' format O 'dd-MMM-yyyy' "

compiller said

TypeError:Cannot read property "rows" object undefined. (row 53, file qvizQuery)

and row 53 is

rows = table.rows.map(function(row) {

Please help me to solve this problem.

Problem was solve! Thanks @Dimu Designs. So everybody who want use this method please open my googleSheet and make COPY and you will be able to learn how script work. if spreadsheet is not available code of solve below

function testQuery() {
   var ssId = SpreadsheetApp.getActive().getId();
   //var ssId = SpreadsheetApp.openById("ID"); // external spreadsheet ID
   var query = "SELECT A, B, O WHERE (O >= date '2020-01-01') AND  (O < date '2020-02-01')";
   //var query = "SELECT A, B, O WHERE O IS NOT NULL";
   var range = "A3:O";
   var sheetName = "SOURCE";
   //var sheetName = "main_data";

   var rows = Utils.gvizQuery(
       ssId,
       query,
       sheetName,
       range
   );

   var result = rows.map(function(row) {
       var [projectId, block, planEnd] = row; // destructuring assignment

       return [projectId, block, eval("new "+ planEnd)]; // return [projectId, block, eval(planEnd)];
   });

   Logger.log(result);

   var sheet =  SpreadsheetApp.getActive().getSheetByName("qvizQuery"); 
   var maxRow = sheet.getMaxRows();
   sheet.getRange(1,1,maxRow, 3).clearContent();
   //removeEmptyRows(sheet);
   sheet.getRange(1, 1, result.length , 3).setValues(result); 
   sheet.getRange(1,5).setFormula("QUERY({SOURCE!A:Z};\"select Col1,Col2,Col15 where (Col15>=date '2020-01-01') and  (Col15 <  date '2020-02-01') \";0)");

In Google Sheets, rows start from index 1, so try 1 instead of zero for the last parameter. — TheAddonDepot, Sep 17 '19 at 11:49
@DimuDesigns when i use `var result = Utils.gvizQuery( "id_spreadshet", "SELECT A,B,O " , "РЕЕСТР2", 0);` this works. But when i use `var result = Utils.gvizQuery( "id_spreadshet", "SELECT A,B,O " , "РЕЕСТР2", 1);` compiller write `Cannot convert Array to number [] []. (line 7, file test_gvizQuery)` and line 7 is my code `sheet_2.getRange(1, 1, 3868, 3).setValues(result);` — Dmitriy Lavrov, Sep 17 '19 at 13:11
in logs it looks like `result = [object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object ...` — Dmitriy Lavrov, Sep 17 '19 at 13:13
Change `sheet_2.getRange(1, 1, 1987, 3).setValues(result)` to `sheet_2.getRange(1, 1, 1987, 3).setValues([result])` — AMolina, Sep 17 '19 at 13:23
When you specify a header row index the function returns an array of objects where the header names are used as keys. if you do not specify a header row index (ie. leave the 4th parameter undefined) then you get an array of arrays. The problem is when you pass 0 as a value for the header row. 0 is recognized as a valid input, but you're not supposed to use it. — TheAddonDepot, Sep 17 '19 at 13:23
So try this instead. `var result = Utils.gvizQuery("id_spreadsheet", "SELECT A, B, O ...", "PEECTP2");` note that I left out the last argument entirely. This ensures its returned as `undefined`, so the `headers` param is not added to the datasource url and with the `headers` variable being undefined an array of arrays is generated, not an array of objects. — TheAddonDepot, Sep 17 '19 at 13:27
@AMolina thank you. I tried you code. Its work. Function retrun 3800 columns withi next cell content `"{Квартал=СД21, АБГС->ГС. Плановая дата выпуска =null, ID проекта=3}"` but I need a little different. i need to get result like after function `=query` — Dmitriy Lavrov, Sep 17 '19 at 13:52
@DimuDesigns thank you. when i use `var result = Utils.gvizQuery( "ID_spreadsheet", "SELECT A,B,O " , "РЕЕСТР2");` its work great. But i need to filter by dates. And when i try to use `var result = Utils.gvizQuery( "ID_spreadsheet", "SELECT A,B,O WHERE O > date '01-01-2019' format O 'dd-MMM-yyyy' " , "РЕЕСТР2");` it does not work. — Dmitriy Lavrov, Sep 17 '19 at 13:57
I can only think of two possible issues. The O column probably needs to be formatted as dates (can be set from the menu via **Format > Number > Date**). And/Or if the column has data that is not a date (if its a header cell) then you need to omit that data. For example, if you wanted fetch the first 5 columns but omit the first row from consideration, then you'd reference a range on the sheet (starting from the 2nd row) as follows- `Utils.gvizQuery("ID_spreadsheet", "SELECT A, B, C, D, E ...", "PEECTP2!A2E")` — TheAddonDepot, Sep 17 '19 at 14:06
@DimuDesigns i tried and tre result is null. Also i tried to chage FORMAT in source - the same result. Was shok to me why function return date as `Date(2019,4,5)`. — Dmitriy Lavrov, Sep 18 '19 at 07:55
@DimuDesigns this is link to my [spreadsheet](https://docs.google.com/spreadsheets/d/1BUb4qXFRjECOZ1poDHGqOT4X4gCPUcJmk5qfc9Vjv8k/edit?usp=sharing) - with scripts. I open it for all. Please can you try it? I dont understend the reason of problem. — Dmitriy Lavrov, Sep 18 '19 at 07:56
@DmitriyLavrov I see the issue now. Google's Visualization Query API returns dates as strings of the form `Date(yyyy-mm-dd)`. You'll have to convert those to dates manually. You can run the result set through the array `map` function to do the conversion. You should be able to use the `eval` function with those strings to convert them to actual `Date` instances. I've also updated the module (here on stackoverflow) to support selecting specific ranges (my previous comment about selecting specific ranges was incorrect). — TheAddonDepot, Sep 18 '19 at 12:08
@DmitriyLavrov added the updated module to the sheet script and an example function `testQuery` showing how its done. — TheAddonDepot, Sep 18 '19 at 12:51
@DimuDesigns thanks! I am beginner in JS. Can you tell me is it right way? `var result_new = result.map(function(i) { return i.map(function(j) { return j.convert() // some function which convert Date(2019,4,5) to 05.04.2019 }); });` — Dmitriy Lavrov, Sep 18 '19 at 15:25
@DmitriyLavrov I updated the script attached to your sample sheet with an example. — TheAddonDepot, Sep 18 '19 at 16:00
@DimuDesigns THANKS YOU A LOT! You are great man! Its work. I added one thing **"new "+** . So my code is `return [projectId, block, eval("new "+ planEnd)];` and now the dates out normaly! — Dmitriy Lavrov, Sep 19 '19 at 06:32
@DimuDesigns and one litttle question) when i try use spreadsheets by id `var ssId = SpreadsheetApp.openById("ID_SS")` show error. `Failed to send the request to https://docs.google. > Error code: 404.` — Dmitriy Lavrov, Sep 20 '19 at 06:55
@DmitriyLavrov Congrats on solving your problem. Rather than leaving the solution in your spreadsheet (which, one day, may not exist), please submit an answer to your own question. It will get indexed, it will be easy for others to find and understand and it won't require others to trawl through the comments. — Tedinoz, Sep 23 '19 at 00:51

Query Language Reference: request by dates

0 Answers0