I have a google sheet Here. It has three subsheets. CONFIDENTIAL : MIS, CONFIDENTIAL : MSA and Collection Sheet. I need a menu button which should read all data after 4th row of the both spreadsheets. And paste in the "Collection Sheet" as a single list, together with current date in E5 to last filled column. The following code does it well but I thing it is not a smart way to do it. And sometimes if the rows are in short in number, it gives "Rows out of range" msg, Would anyone expert give / suggest me some advice to improve it?

The code is given below.

function create_submit_sheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var target_sheet = SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Collection Sheet'))
  target_sheet.getRange('C1').setValue('');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source_sheet = ss.getSheetByName("CONFIDENTIAL : MIS");
  var Avals = source_sheet.getRange("B4:B").getValues();
  var Alast = Avals.filter(String).length;
  //Logger.log(Alast);
  var target_sheet = ss.getSheetByName("Collection Sheet");
  //var roww = target_sheet.getDataRange.length;
  //var Avals = ss.getRange("A1:A").getValues();
  //var Alast = Avals.filter(String).length;
  //Logger.log(Alast);
  var source_range = source_sheet.getRange("B4:F" Alast 4);
  //var target_range = target_sheet.getRange("A6:H");
  var last_row = target_sheet.getLastRow();
  //target_sheet.insertRowAfter(last_row);
  var target_range = target_sheet.getRange("A" (last_row 1) ":C" (last_row 1));
  source_range.copyTo(target_range,{contentsOnly: true});



  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source_sheet = ss.getSheetByName("CONFIDENTIAL : MSA");
  var Avals = source_sheet.getRange("B5:B").getValues();
  var Alast = Avals.filter(String).length;
  var Alast1 = Alast;
  //Logger.log(Alast);
  var target_sheet = ss.getSheetByName("Collection Sheet");
  //var roww = target_sheet.getDataRange.length;
  //var Avals = ss.getRange("A1:A").getValues();
  //var Alast = Avals.filter(String).length;
  //Logger.log(Alast);
  var source_range = source_sheet.getRange("B5:F" Alast 5);
  //var target_range = target_sheet.getRange("A6:H");
  var last_row = target_sheet.getLastRow();
  //target_sheet.insertRowAfter(last_row);
  var target_range = target_sheet.getRange("A" (last_row 1) ":C" (last_row 1));
  source_range.copyTo(target_range,{contentsOnly: true});

  //SpreadsheetApp.getActiveSheet().getRange('F2').setValue('Hello');
  target_sheet.getRange('F4').setValue('প্রদত্ত');
  target_sheet.getRange('G4').setValue('তারিখ');
  var curDate = Utilities.formatDate(new Date(), "GMT 6", "MM/dd/yyyy")
  Avals = target_sheet.getRange("A1:A").getValues();
  Alast = Avals.filter(String).length;
  target_sheet.getRange(5,7, Alast-4 ).setValue(curDate);
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Collection Sheet'))


}

CodePudding user response：

Try the appendRows_() utility function. You can use it like this:

function appendToSubmitSheet() {
  const ss = SpreadsheetApp.getActive();
  const targetSheet = ss.getSheetByName('Collection Sheet');
  const values =
    ss.getRange('CONFIDENTIAL : MIS!B4:F').getValues()
      .concat(ss.getRange('CONFIDENTIAL : MSA!B4:F').getValues())
      .filter(row => row.join(''));
  appendRows_(targetSheet, values, 2);
}