Question 371125 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – How can I escape the [] inside the query function – Apps Script – Google Sheets

I’m trying to merge the data from all the sheets into one sheet. How can I escape the [] inside the query function?

function listAllProducts() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();

  // Start from index 1 to skip the first sheet
  for (var i = sheets.length - 1; i > 1; i--) {
    QUERY({sheets[i]!A2:Z},"select * where Col1 is not null");
  }   
}

Google Sheets – Google App Script

I was expecting the names of each sheet to automatically be populated to the query so I manually don’t have to type each query. Each sheet varies in length and size and there are numerous sheets. Perhaps there is a better way to do this? Any help would be appreciated, Thank you!

Tags:

2

Answers


  1. 0

    Iterate over the sheets starting with an index of 1 and get the data range from the active sheet.

    function listAllProducts() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();

    // Start from index 1 to skip the first sheet
    for (var sheetIdx = 1; sheetIdx < sheets.length; sheetIdx++) {
      //Retrieve the active sheet
      var sheet = sheets[sheetIdx];
      //Retrieve the dataRange of interest from the sheet
      var dataRange = sheet.getRange("A2:Z");
      //The above will get around you [ problem; however it
      //appears you can not call the QUERY sheet function from 
      //App Script.  You can not call sheet functions from App 
      //Script.  You could set this as a formula within a cell, 
      //but not sure exactly what you are looking to do??  
      //QUERY(dataRange,"select * where Col1 is not null");
    }   
}

    Note: you can not call the Google Sheet function QUERY from Google App Script. If you provide more context maybe we can provide a solution for the final step.

    Login or Signup to reply.
  2. 0

    List all sheet contents in an array of 2d arrays with labels on top row of each sheet data array

    function listAllProducts() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const incl = ["Sheet0","Sheet1","Sheet2"];//sheets to include
  const mshn = "Sheet4";//master sheet
  const msh = ss.getSheetByName(mshn);
  msh.clearContents();
  const shts = ss.getSheets().filter(sh => ~incl.indexOf(sh.getName()));
  const dA = [];
  shts.forEach(sh => {
    let vs = sh.getDataRange().getValues().filter(r => r[0] != "").filter(e => e);
    let nA = [...new Array(vs[0].length)];
    nA[0] = sh.getName();
    vs.unshift(nA);
    dA.push(vs);//put sheet name in list
  });
  //Logger.log(JSON.stringify(dA));
  dA.forEach((arr => {
    msh.getRange(msh.getLastRow() + 1,1,arr.length,arr[0].length).setValues(arr);
  })); 
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search