0

For example in the new google sheet I have these formulas :

=Query(ImportRange("ehd54...dfdfg", "'Sheet1'!A:C"),"Select * Where Col1 = 'COM' limit 5" ,1)")

=Query(ImportRange("ehd54...dfdfg", "'Sheet1'!A:C"),"Select * Where Col2 contains 'IS_A_NAME' limit 5")

Notes:

Community
  • 1
  • 1
miodf
  • 524
  • 3
  • 9
  • 21

1 Answers1

1

Once you've "imported" the data, use the 2D Array Library to filter it, then write the results to your sheet.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Many thanks Mogsdad. ;) I have tested your solution. It seems to work great. ;) But I am blocked on a bug. I have added an example code in the post above because I have difficulties pasting the array in my sheet. Thanks in advance ;) – miodf Oct 24 '14 at 13:46
  • I have another request. "ArrayLib.filterByText" is working great if I have `Select * Where Col2 contains 'IS_A_NAME'` but how do I add a limit in order to keep only 5 results ? I have tried `"sh_choix.getRange(37, 1, 5, result[0].length).setValues(result);"` but that doesn't work if I have less than 5 results -for instance 4- ("Incorrect range height, was 4 but should be 5)" ! Thanks in advance ;) – miodf Oct 24 '14 at 15:45
  • `sh_choix.getRange(37, 1, result.length, result[0].length).setValues(result);` – Mogsdad Oct 24 '14 at 16:19
  • Thanks Magstad. I indeed use your previous comments above about my error with the capital S letter. ;) But my new problem is that I would like to limit the results to 5 maximum even if I have more results than 5. Thanks in advance ;) – miodf Oct 24 '14 at 16:32
  • BTW, I've just found that in the library there is a static version number 23 which adds "ArrayLib.filterByValue". Example: you are looking exactly for = "COM" and it finds exactly "COM" not "ACOM" or "COMMY" (which is what "ArrayLib.filterByText" does). – miodf Oct 24 '14 at 18:40
  • For the limit option, I think I have a workaround which is not really great (because it uses google server running time for nothing) but it should do the work. In Sheet1 in A1:C5 I add formulas : A1= Sheet2!A1 ...etc... C5= Sheet2!C5. And I copy/paste the setValues in sheet2. Like that I don't care if there are more than 5 results in sheet2 as I have only 5 results in Sheet1. What do you think ? – miodf Oct 24 '14 at 18:45
  • PS: I realize I have forgotten to explain why I would like to use Google Apps Script instead of google formulas is that I have 6 "=Query(ImportRange" running at the same time in a (new) google sheet. Even if the sheet once exported as an xlsx file weight less then 0.5MB, these 6 queries takes in Firefox about 1500MB of RAM in total on my PC (and it has some difficulties to open it). So I can't open the google sheet with a tablet or a smaller PC. – miodf Oct 24 '14 at 18:57
  • 2
    How to limit number of results? `results=results.slice(0,5)` would do the trick. If you need more, please ask another new question. – Mogsdad Oct 24 '14 at 19:52