Is it possible to make requests to an array? I get the error undefined.
var Journal = []
var JournalFin = Query (Journal, "Select * WHERE (Journal [1] == '2586')")

  • Judging from the fact that you have var Journal =[] - this is an empty array because Journal[1] returns undefined to you. - Dmitriy Kondratiuk

1 answer 1

This question has no definite and expected answer. You need to consider several alternative approaches.

1. Pure JS

The approach uses the capabilities of the GS language, which is a direct descendant of JS. The filter() or reduce() array methods are great:

 function pureJS(data, journalIndex) { var journal = data; var journalFin = journal.filter(function(row){ return row[4] === this.val; }, {val: journalIndex}); return journalFin; }

2. AlaSQL

A library that allows you to apply SQL queries directly to JS data. To install, you need to connect the library or own porting https://github.com/oshliaer/alasqlgs

 function alaSQL(data, journalIndex) { var alasql = AlaSQLGS.load(); alasql.options.modifier = 'MATRIX'; var journal = data; var journalFin = alasql("SELECT * FROM ? AS data WHERE data.[4]=?", [journal, journalIndex]); return journalFin; }

3. "Distortion" formulas

The ambiguous name of the approach means that you can think of. This method is sometimes used, but only in the absence of alternatives. The principle is simple: create a formula, wait for its recalculation, collect the results of its calculations.

 function formulaTweak(spreadsheet, rangeAddr, journalIndex) { var sheet = getSheetByName(spreadsheet, "yabadabaduuu_rnd"); sheet.clear() .getRange("A1") .setFormula(Utilities.formatString('=QUERY(%s; "SELECT * WHERE E=%s")', rangeAddr, journalIndex)); SpreadsheetApp.flush(); var journalFin = sheet.getDataRange().getValues(); journalFin.shift(); return journalFin; }

Data and full listing of the example program can be seen here.

  • Thank! Those. it turns out "vlob" it is impossible to apply query to an array in GS, is it a function of a vorkshit? Need to use alternative methods that you list? - Sturmer
  • In some applications, there are interpreters, but not in the case of Google. - oshliaer