How to get specific cell values from all sheets in a google spreadsheet and list the values in a new sheet using appscript

I am trying to get the names of all my sheets and get specific cell values from each of it, and list those values with its corresponding sheetname in another sheet which will be created upon running the appscript code.

I came up with this:

  var sheetID = "1tGjn4slbC2x7jtMDO7J6m-aWwvHWq2KJdtF4gT9kcEs"; 
  var allSheets = SpreadsheetApp.openById(sheetID).getSheets();

  //New Sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet().setName('LEAVEBAL');

  for (var i = 0; i < allSheets.length; i++) {
    var sheetname = allSheets[i].getName();
    var sheettest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);

    Logger.log(sheetname);
    sheet.getRange(i + 1, 1).setValue(sheetname);

    var vals = sheettest.getRange(sheettest.getLastRow(),12, 1, 4).getValues();

      for (var j = 0; j < vals.length; j ++){
        Logger.log(vals[j]);
        
        sheet.getRange(j + 1, 2).setValues(vals[j]);
      }

Which results in an error. My question is, how can I get a result with a pattern like:

Sheet Name | Value 1 | Value 2 | Value 3 | Value 4

Solution

Try this. A few comments, getSheets() returns an array of sheets so you don't need to getSheetByName(). Second insert your sheet after you have looped through all the existing sheets otherwise you will get that one too. Third use the Best Paractices of avoiding getValue(s)/setValue(s) in a loop. Collect all of your new rows in an array say results and then one setValues().

function myTest() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheets = spread.getSheets();

    let results = [];

    sheets.forEach( sheet => {
        let name = sheet.getName();
        console.log(name);
        let row = [name];
        let lastRow = sheet.getRange(sheet.getLastRow(),1,1,4).getValues();
        console.log(lastRow);
        row = row.concat(lastRow[0]);
        results.push(row);
      }
    );

    sheet = spread.insertSheet('LEAVEBAL');
    sheet.getRange(1,1,results.length,results[0].length).setValues(results);

  }
  catch(err) {
    console.log(err);
  }
}

Reference