Is there a script to do the function of query and import range combined

Question

Need a script to perform function of query and import range. script should import specific columns and rows only with provided conditions.

function UpdateRange() { var values = SpreadsheetApp.openById('Source'). getSheetByName('Dump').getRange('A:Q').getValues(); SpreadsheetApp.getActive().getSheetByName('DATA'). getRange(1,1,values.length,values[0].length).setValues(values); //destination as getrange([row], [col] }

=Query(IMPORTRANGE("source sheet","A:AC"),"Select Col2,Col3,Col4,Col6,Col29,Col9,Col12,Col17,Col25 where Col4 contains date'"&TEXT(TODAY(),"yyyy-MM-dd")&"' and Col11 contains'' and Col17 contains 'cancelled' and Col7 contains 'City'",1)

Need to perform the action of the formulae mentioned in the code

contributorpw · Answer 1 · 2019-03-29T09:26:14.887

For the simplest way I use AlaSQL

function importrangequery(){
  var alasql = AlaSQLGS.load();
  var data =  SpreadsheetApp
    .openById("14I34TM84uBnVILA3KQh9Y2LZr_Tr_fMsDQI2kYfht-E")
    .getSheets()[0]
    .getDataRange()
    .getValues()
    .slice(0, 1000);
  var res = alasql('\
SELECT MATRIX t.[0], t.[1] \
FROM ? as t \
WHERE t.[1] LIKE "%wq%" OR t.[1] LIKE "%uu%"',
                   [data]);
  Logger.log(res);

/* 
  // To insert into another table

  var output = SpreadsheetApp
    .openById("10uS4_YD4z2Ea-UnxkdydxB6QAKJ9GQUQX4Ozvw0F13o")
    .getSheetByName('Output');
  output.getDataRange().clearContent();
  output.getRange(1,1,res.length, res[0].length).setValues(res);
*/

}

The lib https://github.com/oshliaer/alasqlgs

The example https://drive.google.com/drive/folders/1iG34CHDVBIwqG8yOcjJYHl3gx1IsTzOJ

There are other ways for this https://stackoverflow.com/a/28751946/1393023

Is there a script to do the function of query and import range combined

1 Answers1