CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Tamjid Taha
Tamjid Taha

Reputation: 171

How to create a string from the sheet names using Google App Scripts?

I am trying to create a string by adding all sheet names (having both alphabets and numbers in their names). The exact string I need to generate is starting from =QUERY to 0) where PHY4101, MATH4103, HUM4105, ICTE4115 etc. comes from the sheet names of a spreadsheet. Finally the string will be set as a value to A2 cell of Master sheet. How to fix it?

The exact string to produce: =QUERY({PHY4101!A2:F;MATH4103!A2:F;HUM4105!A2:F;ICTE4115!A2:F;ICTE4117!A2:F;ICTE4143!A2:F;PHY4102!A2:F;HUM4106!A2:F;ICTE4144!A2:F},"select * where Col3 is not null",0)

function formatSheetsP3() {
  var sss = SpreadsheetApp.getActiveSpreadsheet();
  
  const allsheets = sss.getSheets().filter(sh => /\d/.test(sh.getName()));    
  for(var s in allsheets){
    var sheet = allsheets[s].getName();
    var str = '=QUERY({'+ sheet + '!A2:F;'
  } 
  str2 = str + '},"select * where Col3 is not null",0)';
  var master = sss.getSheetByName("Master");
  master.getRange("A2").setValue(str2);
}

Upvotes: 1

Views: 214

Answers (2)

TheMaster
TheMaster

Reputation: 50453

Map, filter and join:

const formula = `=QUERY({${sss
  .getSheets()
  .map(sh => `${sh.getName()}!A2:F`)
  .filter(sh => /\d\!A2:F$/.test(sh))
  .join(';')}},"select * where Col3 is not null",0)`;

Upvotes: 3

Marios
Marios

Reputation: 27350

You can simplify your code even more.

Here is the solution:

function formatSheetsP3() {
  let sss = SpreadsheetApp.getActiveSpreadsheet();
  let allsheets = sss.getSheets().filter(sh => /\d/.test(sh.getName())).map(n=>n.getName());
  let mid ='';    
  allsheets.forEach((sh,index)=>{
     let temp = index>0 ? `;${sh}!A2:F`:`${sh}!A2:F`;
     mid+=temp;
  })
  let str2 = `=QUERY({${mid}},"select * where Col3 is not null",0)`
  let master = sss.getSheetByName("Master");
  master.getRange("A2").setValue(str2); 
}

Upvotes: 2

Related Questions