Missing Headers row and Leaving space

user_19442543 0
Viewed 47

I was working with a combine sheets app script which is working fine at the spot by mereging A bunch of 36 sheetsinto one masterenter code here the only flaw of the script is that it is not showing a common header row in the top and also leaving a space after data end from first sheets and so on >>> here the code can anyone help a bit here

function combineSheets() {
  var sApp = SpreadsheetApp.getActiveSpreadsheet();
  var s1= sApp.getSheetByName("A1");
  var s2= sApp.getSheetByName("A2");
  var s3= sApp.getSheetByName("A3");
  var s4= sApp.getSheetByName("A4");
  var s5= sApp.getSheetByName("A5");
  var s6= sApp.getSheetByName("A6");
  var s7= sApp.getSheetByName("A7");
  var s8= sApp.getSheetByName("A8");
  var s9= sApp.getSheetByName("A9");
  var s10= sApp.getSheetByName("A10");
  var s11= sApp.getSheetByName("A11");
  var s12= sApp.getSheetByName("A12");
  var s13= sApp.getSheetByName("A13");
  var s14= sApp.getSheetByName("A14");
  var s15= sApp.getSheetByName("A15");
  var s16= sApp.getSheetByName("A16");
  var s17= sApp.getSheetByName("A17");
  var s18= sApp.getSheetByName("A18");
  var s19= sApp.getSheetByName("A19");
  var s20= sApp.getSheetByName("A20");
  var s21= sApp.getSheetByName("A21");
  var s22= sApp.getSheetByName("A22");
  var s23= sApp.getSheetByName("A23");
  var s24= sApp.getSheetByName("A24");
  var s25= sApp.getSheetByName("A25");
  var s26= sApp.getSheetByName("A26");
  var s27= sApp.getSheetByName("A27");
  var s28= sApp.getSheetByName("A28");
  var s29= sApp.getSheetByName("A29");
  var s30= sApp.getSheetByName("A30");
  var s31= sApp.getSheetByName("A31");
  var s32= sApp.getSheetByName("A32");
  var s33= sApp.getSheetByName("A33");
  var s34= sApp.getSheetByName("A34");
  var s35= sApp.getSheetByName("A35");
  var s36= sApp.getSheetByName("A36");
 var s37= sApp.getSheetByName("Master");
  
  
  //  If Master doesn't exist you'll need to create it here.
  
  var s1values = s1.getRange(2,1,s1.getLastRow(),27).getValues();
  var s2values = s2.getRange(2,1,s2.getLastRow(),27).getValues();
  var s3values = s3.getRange(2,1,s3.getLastRow(),27).getValues();
  var s4values = s4.getRange(2,1,s4.getLastRow(),27).getValues();
  var s5values = s5.getRange(2,1,s5.getLastRow(),27).getValues();
  var s6values = s6.getRange(2,1,s6.getLastRow(),27).getValues();
  var s7values = s7.getRange(2,1,s7.getLastRow(),27).getValues();
  var s8values = s8.getRange(2,1,s8.getLastRow(),27).getValues();
  var s9values = s9.getRange(2,1,s9.getLastRow(),27).getValues();
  var s10values = s10.getRange(2,1,s10.getLastRow(),27).getValues();
  var s11values = s11.getRange(2,1,s11.getLastRow(),27).getValues();
  var s12values = s12.getRange(2,1,s12.getLastRow(),27).getValues();
  var s13values = s13.getRange(2,1,s13.getLastRow(),27).getValues();
  var s14values = s14.getRange(2,1,s14.getLastRow(),27).getValues();
  var s15values = s15.getRange(2,1,s15.getLastRow(),27).getValues();
  var s16values = s16.getRange(2,1,s16.getLastRow(),27).getValues();
  var s17values = s17.getRange(2,1,s17.getLastRow(),27).getValues();
  var s18values = s18.getRange(2,1,s18.getLastRow(),27).getValues();
  var s19values = s19.getRange(2,1,s19.getLastRow(),27).getValues();
  var s20values = s20.getRange(2,1,s20.getLastRow(),27).getValues();
  var s21values = s21.getRange(2,1,s21.getLastRow(),27).getValues();
  var s22values = s22.getRange(2,1,s22.getLastRow(),27).getValues();
  var s23values = s23.getRange(2,1,s23.getLastRow(),27).getValues();
  var s24values = s24.getRange(2,1,s24.getLastRow(),27).getValues();
  var s25values = s25.getRange(2,1,s25.getLastRow(),27).getValues();
  var s26values = s26.getRange(2,1,s26.getLastRow(),27).getValues();
  var s27values = s27.getRange(2,1,s27.getLastRow(),27).getValues();
  var s28values = s28.getRange(2,1,s28.getLastRow(),27).getValues();
  var s29values = s29.getRange(2,1,s29.getLastRow(),27).getValues();
  var s30values = s30.getRange(2,1,s30.getLastRow(),27).getValues();
  var s31values = s31.getRange(2,1,s31.getLastRow(),27).getValues();
  var s32values = s32.getRange(2,1,s32.getLastRow(),27).getValues();
  var s33values = s33.getRange(2,1,s33.getLastRow(),27).getValues();
  var s34values = s34.getRange(2,1,s34.getLastRow(),27).getValues();
  var s35values = s35.getRange(2,1,s35.getLastRow(),27).getValues();
  var s36values = s36.getRange(2,1,s36.getLastRow(),27).getValues();
  
  //  Now, we can put out all together and stuff it in Master
  var s37values = [];
  s37values =  s1values.concat(s2values,s3values,s4values,s5values,s6values,s7values,s8values,s9values,s10values,s11values,s12values,s13values,s14values,s15values,s16values,s17values,s18values,s19values,s20values,s21values,s22values,s23values,s24values,s25values,s26values,s27values,s28values,s29values,s30values,s31values,s32values,s33values,s34values,s35values,s36values);
  s37.getRange(1,1,s37values.length,27).setValues(s37values);

  
}
google-apps-script google-sheets
1 Answers

Modification points:

  • About not showing a common header row in the top, in your script, all values are retrieved from the 2nd row. I thought that this might be the reason for your issue. From a common header row, in this modification, the header row is retrieved from the 1st sheet.
  • About also leaving a space after data end from first sheets and so on, I thought that the reason of your issue is due to getRange(2,1,s1.getLastRow(),27). In this case, the values are retrieved from 2nd row to the last row + 1 rows. In this case, it required to be getRange(2,1,s1.getLastRow() - 1,27).
  • In your script, I thought that the sheet names from "A1" to "A36" can be created using a script.

When these points are reflected in your script, it becomes as follows.

Modified script:

function combineSheets() {
  var sApp = SpreadsheetApp.getActiveSpreadsheet();
  var values = [...Array(36)].map((_, i) => `A${i + 1}`).flatMap((s, i) => {
    var sheet = sApp.getSheetByName(s);
    var lastRow = sheet.getLastRow();
    if (i == 0) {
      return sheet.getRange(1, 1, lastRow, 27).getValues();
    }
    return lastRow > 1 ? sheet.getRange(2, 1, lastRow - 1, 27).getValues() : [];
  });
  sApp.getSheetByName("Master").getRange(1, 1, values.length, 27).setValues(values);
}
  • The headr row is retrieved from the 1st sheet.

Note:

  • In this modification, it supposes that the sheet names of "A1" to "A36" and "Master" are existing in your Spreadsheet. Please be careful about this.

  • From a common header row in the top, this script supposes that your all sheets have the same header row. Please be careful about this.

  • About Exception: The number of rows in the range must be at least 1. AT HERE................var values = [...Array(36)].map((_, i) => A${i + 1}).flatMap((s, i) => { var sheet = sApp.getSheetByName(s); return sheet.getRange(i == 0 ? 1 : 2, 1, sheet.getLastRow() - (i == 0 ? 0 : 1), 27).getValues(); , from your question, I had thought that your all sheets have the values. But from your reply, it seems that my understanding was not correct. So, I updated my proposed script. Could you please confirm it?

References:

user_71086530
Related