0

I am writing a Google script. I'm using AlaSql. It is necessary to make a request with a filter by date. But I don’t understand how to convert the date. The result is incorrect.

Пишу гугл скрипт. Использую AlaSql. Надо сделать запрос с фильтром по дате. Но не понимаю как преобразовать дату. Результат выдает неверный.

I tried to use one of the options from the site Пытался использовать один из вариантов с сайта http://tigor.com.ua/blog/2008/08/23/date_comparison_by_between_operator_of_mysql/

But not one fits. Но ни один не подходит.

My code is ...

 var range = sheet.getDataRange();
 var data_sheet = range.getValues();

...

  var left = new Date(2018,00,01);
  var right = new Date(2020, 09, 12);
  var date_left = Utilities.formatDate( left, "GMT" , "dd-MM-yyyy");
  var date_right = Utilities.formatDate( right, "GMT" , "dd-MM-yyyy");
  Logger.log("left = " + left); // result: left = Mon Jan 01 2018 00:00:00 GMT+0300 (MSK)
  Logger.log("date_left = " + date_left); // result:  date_left = 31-12-2017
  Logger.log("date_right = " + date_right); // result:  date_right = 11-10-2019

  var sql = "select Col1, Col2, Col4, Col8, Col15 from ?  where Col15 BETWEEN '"+ date_left +"' AND '"+ date_right +"' ";   

  var data = getAlaSql(sql, data_sheet); // to alasql

But result is null rows and null columns.

The google Sheet contain a 3500 rows. So i exactly know that there are date which match to conditions. enter image description here

Also i tried method4 function from here My code is 

function UsingtheQueryLanguage() {
var spreadsheetID = 'Id_***';
 var sheetName = 'РЕЕСТР2';
 var queryColumnLetterSearch = 'B';
 var query = 'СД21';
 var queryColumnLetterStart = 'A';
 var queryColumnLetterEnd = 'Z';
 var data = method4(spreadsheetID, sheetName, queryColumnLetterStart, queryColumnLetterEnd, queryColumnLetterSearch, query);
 var sheet_2 =  SpreadsheetApp.getActive().getSheetByName("Using_the_Query_Language"); 
 sheet_2.getRange(1, 1).setValue(data); // запист результата на лист. data.length - длинна строк,data[0].length - длинна стлобцов

}

function method4(spreadsheetID, sheetName, queryColumnLetterStart, queryColumnLetterEnd, queryColumnLetterSearch, query)
{
    // find the last row in the sheet
    var lastRow = SpreadsheetApp.openById(spreadsheetID).getSheetByName(sheetName).getLastRow();

    // SQL like query
    myQuery = "SELECT A,B,C,D,O  WHERE " + queryColumnLetterSearch + " = '" + query + "'";

    // the query URL
    var qvizURL = 'https://docs.google.com/spreadsheets/d/' + spreadsheetID + '/gviz/tq?tqx=out:json&headers=1&sheet=' + sheetName + '&range=' + queryColumnLetterStart + "1:" + queryColumnLetterEnd + lastRow + '&tq=' + encodeURIComponent(myQuery);
    Logger.log("method4 qvizURL  = " + qvizURL );
    // fetch the data
    var ret = UrlFetchApp.fetch(qvizURL, {headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}}).getContentText();

     Logger.log("method4 ret  = " + ret );
    // remove some crap from the return string
    var return_value = JSON.parse(ret.replace("/*O_o*/", "").replace("google.visualization.Query.setResponse(", "").slice(0, -2));
    Logger.log("method4 return_value  = " + return_value );
    return return_value;

}

but the result of Loggs is JSON alweys. I dont understend how to set result values to sheet.

Logger.log("method4 qvizURL = " + qvizURL ):

method4 qvizURL  = https://docs.google.com/spreadsheets/d/153QlH2KiGugRansZuttt1HcrMO9YX4Nfg0dVEP5BZIY/gviz/tq?tqx=out:json&headers=1&sheet=РЕЕСТР2&range=A1:Z3868&tq=SELECT%20A%2CB%2CC%2CD%2CO%20%20WHERE%20B%20%3D%20'%D0%A1%D0%9421'

Logger.log("method4 ret = " + ret ):

method4 ret  = /*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"2019172810","table":{"cols":[{"id":"A","label":"ID проекта","type":"number","pattern":"General"},{"id":"B","label":"Квартал","type":"string"},{"id":"C","label":"Этап","type":"string"},{"id":"D","label":"Корпус","type":"string"},{"id":"O","label":"АБГС-\u003eГС.\nПлановая дата выпуска\n","type":"date","pattern":"dd.MM.yyyy"}],"rows":[{"c":[{"v":1.0,"f":"1"},{"v":"СД21"},{"v":"Э4"},{"v":"К30"},{"v":"Date(2019,6,25)","f":"25.07.2019"}]},{"c":[{"v":2.0,"f":"2"},{"v":"СД21"},{"v":"Э4"},{"v":"К31"},{"v":null}]},{"c":[{"v":3.0,"f":"3"},{"v":"СД21"},{"v":"Э4"},{"v":"К32"},{"v":null}]},{"c":[{"v":4.0,"f":"4"},{"v":"СД21"},{"v":"Э4"},{"v":"К33"},{"v":null}]},{"c":[{"v":5.0,"f":"5"},{"v":"СД21"},{"v":"Э4"},{"v":"К34"},{"v":null}]},{"c":[{"v":6.0,"f":"6"},{"v":"СД21"},{"v":"Э4"},{"v":"К35"},{"v":null}]},{"c":[{"v":7.0,"f":"7"},{"v":"СД21"},{"v":"Э4"},{"v":"К36"},{"v":null}]},{"c":[{"v":8.0,"f":"8"},{"v":"СД21"},{"v":"Э4"},{"v":"К37"},{"v":null}]},{"c":[{"v":9.0,"f":"9"},{"v":"СД21"},{"v":"Э4"},{"v":"К38"},{"v":null}]},{"c":[{"v":10.0,"f":"10"},{"v":"СД21"},{"v":"Э4"},{"v":"К39"},{"v":null}]},{"c":[{"v":11.0,"f":"11"},{"v":"СД21"},{"v":"Э4"},{"v":"К40"},{"v":null}]},{"c":[{"v":12.0,"f":"12"},{"v":"СД21"},{"v":"Э4"},{"v":"К41"},{"v":null}]},{"c":[{"v":13.0,"f":"13"},{"v":"СД21"},{"v":"Э4"},{"v":"К42"},{"v":null}]},{"c":[{"v":14.0,"f":"14"},{"v":"СД21"},{"v":"Э4"},{"v":"К43"},{"v":null}]},{"c":[{"v":15.0,"f":"15"},{"v":"СД21"},{"v":"Э4"},{"v":"К30"},{"v":null}]},{"c":[{"v":16.0,"f":"16"},{"v":"СД21"},{"v":"Э4"},{"v":"К31"},{"v":null}]},{"c":[{"v":17.0,"f":"17"},{"v":"СД21"},{"v":"Э4"},{"v":"К32"},{"v":null}]},{"c":[{"v":18.0,"f":"18"},{"v":"СД21"},{"v":"Э4"},{"v":"К33"},{"v":null}]},{"c":[{"v":19.0,"f":"19"},{"v":"СД21"},{"v":"Э4"},{"v":"К34"},{"v":null}]},{"c":[{"v":20.0,"f":"20"},{"v":"СД21"},{"v":"Э4"},{"v":"К35"},{"v":null}]},{"c":[{"v":21.0,"f":"21"},{"v":"СД21"},{"v":"Э4"},{"v":"К36"},{"v":null}]},{"c":[{"v":22.0,"f":"22"},{"v":"СД21"},{"v":"Э4"},{"v":"К37"},{"v":null}]},{"c":[{"v":23.0,"f":"23"},{"v":"СД21"},{"v":"Э4"},{"v":"К38"},{"v":null}]},{"c":[{"v":24.0,"f":"24"},{"v":"СД21"},{"v":"Э4"},{"v":"К39"},{"v":null}]},{"c":[{"v":25.0,"f":"25"},{"v":"СД21"},{"v":"Э4"},{"v":"К40"},{"v":null}]},{"c":[{"v":26.0,"f":"26"},{"v":"СД21"},{"v":"Э4"},{"v":"К41"},{"v":null}]},{"c":[{"v":27.0,"f":"27"},{"v":"СД21"},{"v":"Э4"},{"v":"К42"},{"v":null}]},{"c":[{"v":28.0,"f":"28"},{"v":"СД21"},{"v":"Э4"},{"v":"К43"},{"v":null}]},{"c":[{"v":29.0,"f":"29"},{"v":"СД21"},{"v":"Э4"},{"v":"К30"},{"v":null}]},{"c":[{"v":30.0,"f":"30"},{"v":"СД21"},{"v":"Э4"},{"v":"К31"},{"v":null}]},{"c":[{"v":31.0,"f":"31"},{"v":"СД21"},{"v":"Э4"},{"v":"К32"},{"v":null}]},{"c":[{"v":32.0,"f":"32"},{"v":"СД21"},{"v":"Э4"},{"v":"К33"},{"v":null}]},{"c":[{"v":33.0,"f":"33"},{"v":"СД21"},{"v":"Э4"},{"v":"К34"},{"v":null}]},{"c":[{"v":34.0,"f":"34"},{"v":"СД21"},{"v":"Э4"},{"v":"К35"},{"v":null}]},{"c":[{"v":35.0,"f":"35"},{"v":"СД21"},{"v":"Э4"},{"v":"К36"},{"v":null}]},{"c":[{"v":36.0,"f":"36"},{"v":"СД21"},{"v":"Э4"},{"v":"К37"},{"v":null}]},{"c":[{"v":37.0,"f":"37"},{"v":"СД21"},{"v":"Э4"},{"v":"К38"},{"v":null}]},{"c":[{"v":38.0,"f":"38"},{"v":"СД21"},{"v":"Э4"},{"v":"К39"},{"v":null}]},{"c":[{"v":39.0,"f":"39"},{"v":"СД21"},{"v":"Э4"},{"v":"К40"},{"v":null}]},{"c":[{"v":40.0,"f":"40"},{"v":"СД21"},{"v":"Э4"},{"v":"К41"},{"v":null}]},{"c":[{"v":41.0,"f":"41"},{"v":"СД21"},{"v":"Э4"},{"v":"К42"},{"v":null}]},{"c":[{"v":42.0,"f":"42"},{"v":"СД21"},{"v":"Э4"},{"v":"К43"},{"v":null}]},{"c":[{"v":43.0,"f":"43"},{"v":"СД21"},{"v":"Э4"},{"v":"К30"},{"v":null}]},{"c":[{"v":44.0,"f":"44"},{"v":"СД21"},{"v":"Э4"},{"v":"К31"},{"v":null}]},{"c":[{"v":45.0,"f":"45"},{"v":"СД21"},{"v":"Э4"},{"v":"К32"},{"v":null}]},{"c":[{"v":46.0,"f":"46"},{"v":"СД21"},{"v":"Э4"},{"v":"К33"},{"v":null}]},{"c":[{"v":47.0,"f":"47"},{"v":"СД21"},{"v":"Э4"},{"v":"К34"},{"v":null}]},{"c":[{"v":48.0,"f":"48"},{"v":"СД21"},{"v":"Э4"},{"v":"К35"},{"v":null}]},{"c":[{"v":49.0,"f":"49"},{"v":"СД21"},{"v":"Э4"},{"v":"К36"},{"v":null}]},{"c":[{"v":50.0,"f":"50"},{"v":"СД21"},{"v":"Э4"},{"v":"К37"},{"v":null}]},{"c":[{"v":51.0,"f":"51"},{"v":"СД21"},{"v":"Э4"},{"v":"К38"},{"v":null}]},{"c":[{"v":52.0,"f":"52"},{"v":"СД21"},{"v":"Э4"},{"v":"К39"},{"v":null}]},{"c":[{"v":53.0,"f":"53"},{"v":"СД21"},{"v":"Э4"},{"v":"К40"},{"v":null}]},{"c":[{"v":54.0,"f":"54"},{"v":"СД21"},{"v":"Э4"},{"v":"К41"},{"v":null}]},{"c":[{"v":55.0,"f":"55"},{"v":"СД21"},{"v":"Э4"},{"v":"К42"},{"v":null}]},{"c":[{"v":56.0,"f":"56"},{"v":"СД21"},{"v":"Э4"},{"v":"К43"},{"v":null}]},{"c":[{"v":57.0,"f":"57"},{"v":"СД21"},{"v":"Э4"},{"v":"К30"},{"v":null}]},{"c":[{"v":58.0,"f":"58"},{"v":"СД21"},{"v":"Э4"},{"v":"К31"},{"v":null}]},{"c":[{"v":59.0,"f":"59"},{"v":"СД21"},{"v":"Э4"},{"v":"К32"},{"v":null}]},{"c":[{"v":60.0,"f":"60"},{"v":"СД21"},{"v":"Э4"},{"v":"К33"},{"v":null}]},{"c":[{"v":61.0,"f":"61"},{"v":"СД21"},{"v":"Э4"},{"v":"К34"},{"v":null}]},{"c":[{"v":62.0,"f":"62"},{"v":"СД21"},{"v":"Э4"},{"v":"К35"},{"v":null}]},{"c":[{"v":63.0,"f":"63"},{"v":"СД21"},{"v":"Э4"},{"v":"К36"},{"v":null}]},{"c":[{"v":64.0,"f":"64"},{"v":"СД21"},{"v":"Э4"},{"v":"К37"},{"v":null}]},{"c":[{"v":65.0,"f":"65"},{"v":"СД21"},{"v":"Э4"},{"v":"К38"},{"v":null}]},{"c":[{"v":66.0,"f":"66"},{"v":"СД21"},{"v":"Э4"},{"v":"К39"},{"v":null}]},{"c":[{"v":67.0,"f":"67"},{"v":"СД21"},{"v":"Э4"},{"v":"К40"},{"v":null}]},{"c":[{"v":68.0,"f":"68"},{"v":"СД21"},{"v":"Э4"},{"v":"К41"},{"v":null}]},{"c":[{"v":69.0,"f":"69"},{"v":"СД21"},{"v":"Э4"},{"v":"К42"},{"v":null}]},{"c":[{"v":70.0,"f":"70"},{"v":"СД21"},{"v":"Э4"},{"v":"К43"},{"v":null}]},{"c":[{"v":71.0,"f":"71"},{"v":"СД21"},{"v":"Э4"},{"v":"К30"},{"v":null}]},{"c":[{"v":72.0,"f":"72"},{"v":"СД21"},{"v":"Э4"},{"v":"К31"},{"v":null}]},{"c":[{"v":73.0,"f":"73"},{"v":"СД21"},{"v":"Э4"},{"v":"К32"},{"v":null}]},{"c":[{"v":74.0,"f":"74"},{"v":"СД21"},{"v":"Э4"},{"v":"К33"},{"v":null}]},{"c":[{"v":75.0,"f":"75"},{"v":"СД21"},{"v":"Э4"},{"v":"К34"},{"v":null}]},{"c":[{"v":76.0,"f":"76"},{"v":"СД21"},{"v":"Э4"},{"v":"К35"},{"v":null}]},{"c":[{"v":77.0,"f":"77"},{"v":"СД21"},{"v":"Э4"},{"v":"К36"},{"v":null}]},{"c":[{"v":78.0,"f":"78"},{"v":"СД21"},{"v":"Э4"},{"v":"К37"},{"v":null}]},{"c":[{"v":79.0,"f":"79"},{"v":"СД21"},{"v":"Э4"},{"v":"К38"},{"v":null}]},{"c":[{"v":80.0,"f":"80"},{"v":"СД21"},{"v":"Э4"},{"v":"К39"},{"v":null}]},{"c":[{"v":81.0,"f":"81"},{"v":"СД21"},{"v":"Э4"},{"v":"К40"},{"v":null}]},{"c":[{"v":82.0,"f":"82"},{"v":"СД21"},{"v":"Э4"},{"v":"К41"},{"v":null}]},{"c":[{"v":83.0,"f":"83"},{"v":"СД21"},{"v":"Э4"},{"v":"К42"},{"v":null}]},{"c":[{"v":84.0,"f":"84"},{"v":"СД21"},{"v":"Э4"},{"v":"К43"},{"v":null}]},{"c":[{"v":85.0,"f":"85"},{"v":"СД21"},{"v":"Э4"},{"v":"К30"},{"v":null}]},{"c":[{"v":86.0,"f":"86"},{"v":"СД21"},{"v":"Э4"},{"v":"К31"},{"v":null}]},{"c":[{"v":87.0,"f":"87"},{"v":"СД21"},{"v":"Э4"},{"v":"К32"},{"v":null}]},{"c":[{"v":88.0,"f":"88"},{"v":"СД21"},{"v":"Э4"},{"v":"К33"},{"v":null}]},{"c":[{"v":89.0,"f":"89"},{"v":"СД21"},{"v":"Э4"},{"v":"К34"},{"v":null}]},{"c":[{"v":90.0,"f":"90"},{"v":"СД21"},{"v":"Э4"},{"v":"К35"},{"v":null}]},{"c":[{"v":91.0,"f":"91"},{"v":"СД21"},{"v":"Э4"},{"v":"К36"},{"v":null}]},{"c":[{"v":92.0,"f":"92"},{"v":"СД21"},{"v":"Э4"},{"v":"К37"},{"v":null}]},{"c":[{"v":93.0,"f":"93"},{"v":"СД21"},{"v":"Э4"},{"v":"К38"},{"v":null}]},{"c":[{"v":94.0,"f":"94"},{"v":"СД21"},{"v":"Э4"},{"v":"К39"},{"v":null}]},{"c":[{"v":95.0,"f":"95"},{"v":"СД21"},{"v":"Э4"},{"v":"К40"},{"v":null}]},{"c":[{"v":96.0,"f":"96"},{"v":"СД21"},{"v":"Э4"},{"v":"К41"},{"v":null}]},{"c":[{"v":97.0,"f":"97"},{"v":"СД21"},{"v":"Э4"},{"v":"К42"},{"v":null}]},{"c":[{"v":98.0,"f":"98"},{"v":"СД21"},{"v":"Э4"},{"v":"К43"},{"v":null}]},{"c":[{"v":99.0,"f":"99"},{"v":"СД21"},{"v":"Э4"},{"v":"К35"},{"v":null}]},{"c":[{"v":100.0,"f":"100"},{"v":"СД21"},{"v":"Э4"},{"v":"К36"},{"v":null}]},{"c":[{"v":101.0,"f":"101"},{"v":"СД21"},{"v":"Э4"},{"v":"К37"},{"v":null}]},{"c":[{"v":102.0,"f":"102"},{"v":"СД21"},{"v":"Э4"},{"v":"К38"},{"v":null}]},{"c":[{"v":103.0,"f":"103"},{"v":"СД21"},{"v":"Э4"},{"v":"К39"},{"v":null}]},

Logger.log("method4 return_value = " + return_value ):

method4 return_value  = [object Object]
Dmitriy Lavrov
  • 3
  • 5
  • date should be of format `yyyy-mm-dd`. `2018/01/01` is `yyyy/mm/dd`. You can search for `Utilities.formatDate()` in documentation. – TheMaster Sep 12 '19 at 11:11
  • i tried to use Utilities.formatDate(). But AlaSql dont understend it. He wrote error and text ... Error: Parse error on line 1: ...Utilities.formatDate([14], 'GMT', 'dd-MM -----------------------^ Expecting 'RPAR', 'IN', 'LIKE', 'ARROW', 'DOT', 'CARET', 'EQ', 'SLASH', 'EXCLAMATION', 'MODULO', 'GT', 'LT', 'GTGT', 'LTLT', 'NOT', 'AND', 'OR', 'PLUS', 'STAR', 'REGEXP', 'GLOB', 'NOT_LIKE', 'BARBAR', 'MINUS', 'AMPERSAND', 'BAR', 'GE', 'LE', 'EQEQ', 'EQEQEQ', 'NE', 'NEEQEQ', 'NEEQEQEQ', 'BETWEEN', 'NOT_BETWEEN', 'IS', 'DOUBLECOLON', got 'LPAR' (строка 8, файл #AlaSql) – Dmitriy Lavrov Sep 12 '19 at 12:58
  • Write the date as a variable: ``var fromDate= Utilities.formatDate(new Date(2018,00,01),'GMT', 'yyyy-MM-dd');`` Then add it `...BETWEEN '"+ fromDate + "' AND ...` – TheMaster Sep 12 '19 at 13:27
  • The master thank you, but it does not work ( my code is : var sql = "select Col1, Col2, Col4, Col8, Col15 from ? where "+ date_left +" BETWEEN AND "+ date_right +" "; but AlaSql dont understend it and requires some of his function... – Dmitriy Lavrov Sep 12 '19 at 14:47
  • It should be Between date left and date right. In addition, it should be quoted with single quotes and double quotes, as I've shown in previous comment. – TheMaster Sep 12 '19 at 15:00
  • @TheMaster sorry, but i dont understend why it does not work. I made like you wrote. My code is – Dmitriy Lavrov Sep 13 '19 at 09:53
  • @TheMaster ```var left = new Date(2018,00,01); var right = new Date(2019,09,12); var date_left = Utilities.formatDate( left, "GMT" , "dd-MM-yyyy"); var date_right = Utilities.formatDate( right, "GMT" , "dd-MM-yyyy"); Logger.log("left = " + left); // res: left = Mon Jan 01 2018 00:00:00 GMT+0300 (MSK) Logger.log("date_left = " + date_left); // res: date_left = 31-12-2017 Logger.log("date_right = " + date_right); // res: date_right = 11-10-2019 var sql = "select Col1, Col2, Col4, Col8, Col15 from ? where Col15 BETWEEN '"+ date_left +"' AND '"+ date_right +"' "; ``` – Dmitriy Lavrov Sep 13 '19 at 10:05
  • @TheMaster The result of code is ```empty array``` from alasql request – Dmitriy Lavrov Sep 13 '19 at 10:07
  • [Edit] your question to add the latest code and the result. – TheMaster Sep 13 '19 at 10:13
  • @TheMaster i did – Dmitriy Lavrov Sep 13 '19 at 11:04
  • Could you link the `google-apps-script`-`alasql` library you used to access from apps -script? – TheMaster Sep 13 '19 at 11:13
  • If you are not fixed with `alasql`, you can use `google-query-language`. See [doc](https://developers.google.com/chart/interactive/docs/querylanguage#setting-the-query-in-the-data-source-url) and [method3 or 4 here](https://stackoverflow.com/a/56737759) – TheMaster Sep 13 '19 at 11:26
  • @TheMaster i Tried method4. But it did not help me. I added new info to post. Also i use AlaSql. Info about it: https://github.com/agershun/alasql – Dmitriy Lavrov Sep 16 '19 at 11:45
  • Where is alasql library in apps script? Or how exactly are you including alasql in your script editor? Can you provide complete code? Or define function`getAlaSql`? *Tried method4. But it did not help me* what do you mean by *it did not help me* – TheMaster Sep 16 '19 at 11:48
  • @TheMaster Thank you for answer! So, i use like in example sheet. Link to expaple sheet https://docs.google.com/spreadsheets/d/1BrZydeCxJAjtP5xQA-g8FbFv1J29L6Uzd1ZKGbfVubc/edit#gid=0 – Dmitriy Lavrov Sep 16 '19 at 11:56
  • i added to my google sheet scripts all files wich start by # – Dmitriy Lavrov Sep 16 '19 at 11:57
  • Metod4 return only JSON. For exp the value of qvizURL also JSON. And JSON content this – Dmitriy Lavrov Sep 16 '19 at 12:00
  • /*O_o*/ google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"error","errors":[{"reason":"invalid_query","message":"INVALID_QUERY","detailed_message":"Invalid query: PARSE_ERROR: Encountered \u0022 \u003cID\u003e \u0022B \u0022\u0022 at line 1, column 25.\nWas expecting one of:\n \u0022(\u0022 ...\n \u0022(\u0022 ...\n "}]}); – Dmitriy Lavrov Sep 16 '19 at 12:00
  • Your question shows values `setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"2019172810","table":{"cols":[{"id":"A","label":"ID проекта","type":"number","pattern":"General"},{"id":"B","label":"Квартал","type":"string"},{"id":"C","label":"Этап","type":"string"},{"id":"D","label":"Корпус","type":"string"},{"id":"O","label":"АБГС-\u003eГС.\nПлановая дата выпуска\n","type":"date","pattern":"dd.MM.yyyy"}],"rows":[{"c":[{"v":1.0,"f":"1"},{"v":"СД21"},{"v".....`. Now you're showing error? `INVALID_QUERY","detailed_message":"Invalid query: PARSE_ERROR: Encountered \u0022 \u003cID\u...` – TheMaster Sep 16 '19 at 12:05
  • @TheMaster Sorry. My mistake. Old info. You are right. In my Question right info. I follow to link by qvizURL and browser return me JSON to download. Please tell me how write this data to my sheet ? – Dmitriy Lavrov Sep 16 '19 at 12:11
  • Use [this](https://stackoverflow.com/a/51328419/) instead to parse json to array of arrays. You can then `setValues()` easily. – TheMaster Sep 16 '19 at 12:17
  • @TheMaster i tried this. my code ``` var result = Utils.gvizQuery( "id_table", "SELECT A,B,O WHERE O > date '2019-01-01' " , "РЕЕСТР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); ``` It works, but it does not works with dates. For exp in this code date from `30.08.2018` become to `Date(2018,7,30)` – Dmitriy Lavrov Sep 16 '19 at 13:57
  • also filter by date dont work. I tried use format but result is error. I used this `"SELECT A,B,O format O 'dd-MMM-yyyy' WHERE O > date '01-01-2019' "` – Dmitriy Lavrov Sep 16 '19 at 14:00
  • It should be `"SELECT A,B,O WHERE O > date '01-01-2019' format O 'dd-MMM-yyyy' "`. Check timezone of your script editor and see of it matches the one set in your spreadsheet. If you want actual date strings, you need to learn how to parse json yourself and modify the script provided there. – TheMaster Sep 16 '19 at 14:28
  • @TheMaster i checked my zone. by code `var data1 = sheet.getRange(126, 15).getValue(); Logger.log("data1 = " + data1);` i got in log this `data1 = Sat Jul 27 2019 00:00:00 GMT+0300 (MSK)` – Dmitriy Lavrov Sep 16 '19 at 14:58
  • 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) {` – Dmitriy Lavrov Sep 16 '19 at 15:03
  • It means that there are no rows matching your query. Could you ask a new question regarding Google query language and let this question be for alasql? So that others might be able to help – TheMaster Sep 16 '19 at 15:07
  • @TheMaster thank you for your comments! yes, would be better to make new questuin about Query Language Reference. Thank you again! – Dmitriy Lavrov Sep 16 '19 at 15:16

0 Answers0