Home>

In the code below, I want to add the result of adding the elements in value1 and value2 to the variable p2x12. What should I do?
The first half is to copy a specific template for each sheet. The contents of this case are from the 82nd line.
I am ashamed of the dirty code, but I would appreciate it if you could teach me. Thank you.

function copyRange () {
  // get all sheet names
  var ssheet = SpreadsheetApp.getActiveSpreadsheet ();
  var sheets = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
  var sheet_names1 = [];
  var sheet_names = [];
  var hiduke_sheet = [];
  if (sheets.length>= 1) {
    for (var i = 0;i<sheets.length;i ++)
    {
      sheet_names1.push (sheets [i] .getName ());// sheet name array
    }
  }
  for (var m = 0;m<sheet_names1.length;m ++) {
    if ((sheet_names1 [m]! = "aggregate")&&(sheet_names1 [m]! = "master")&&(sheet_names1 [m]! = "template")) {
      hiduke_sheet.push (sheet_names1 [m]);
      sheet_names.push (sheet_names1 [m]);
    }
  }
  // Copy template P26: Q38 to date sheet
  var range_from = ssheet.getSheetByName ("Template"). getRange ('P26: W38');// Template

  for (var l = 0;l<sheet_names.length;l ++) {
    var range_to = ssheet.getSheetByName (sheet_names [l]). getRange ('P26: W38');
    range_from.copyTo (range_to);
  }
  / *******************
  ① If the date is blank, all sheets are totaled
  ② If the start date is blank, the oldest sheet is counted from the specified date.
  ③ If the end date is blank, total from the start date to the latest sheet
  ******************** /
  // Get start date and end date
  var hiduke_range = ssheet.getSheetByName ('Aggregate'). getRange ('M25: N25'). getValues ​​();
  var hiduke_start = [];
  var hiduke_end = [];
  // If the date is listed on the summary sheet, get the date, if not, get the date of the oldest sheet and new sheet
  var date_array = [];// Store the date range sheet name
  for (var n = 0;n<hiduke_sheet.length;n ++) {
    date_array.push (new Date (hiduke_sheet [n]). getTime ());
    }
  //start date
  if (hiduke_range [0] [0]! = "") {
    hiduke_start = new Date (hiduke_range [0] [0]). getTaime ();
  } else {
    hiduke_start = Math.min.apply (null, date_array);
  }
  //End date
  if (hiduke_range [0] [1]! = "") {
    hiduke_end = new Date (hiduke_range [0] [1]). getTime ();
  } else {
    hiduke_end = Math.max.apply (null, date_array);
  }
 // Limit hiduke_sheet from the start date to the end date.
  var copy_sheet = [];
  for (var o = 0;o<hiduke_sheet.length;o ++) {
    if ((hiduke_start<= date_array [o])&&((date_array [o]<= hiduke_end))) {
      copy_sheet.push (Utilities.formatDate (new Date (date_array [o]), "JST", "yyyy/MM/dd"));
    }
  }
  //Logger.log(date_array[0]<= new Date (hiduke_end) .getTime ())
  // Get an array for aggregation
  var p2x12 = [];
  var p14x24 = [];
  var p26q38 = [];

  // p12: total x12 range
  var value1 = ssheet.getSheetByName (copy_sheet [0]). getRange ('q3: x12'). getValues ​​();
  var value2 = ssheet.getSheetByName (copy_sheet [1]). getRange ('q3: x12'). getValues ​​();

  for (var j = 0;j<9;j ++) {// line loop
      for (var k = 0;k<7;k ++) {// sequence loop
        p2x12 [j] [k] = vlue1 [j] [k] + vlue2 [j] [k];
      }
      }

  Logger.log (range_to)
}
  • Answer # 1

    If you write in For

    for (var j = 0;j<9;j ++) {// Line loop
      var row = [];
      for (var k = 0;k<7;k ++) {// sequence loop
        row.push (value1 [j] [k] + value2 [j] [k]);
      }
      p2x12.push (row);
    }

    If you write in Map

    p2x12 = value1.map (function (x, i) {
      return x.map (function (y, j) {
        return y + value2 [i] [j];
      });
    });

Related articles