Untitled

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Automation')
    .addItem('Pull Reports', 'pullReports')
    .addItem('Edit Check', 'checkForEdits')
    .addToUi();
}

function pullReports() {
  var sheets = SpreadsheetApp.getActive().getSheets();
  var machineSheets = [];
  var dataRows = [];
  var reportSheet;
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getName().indexOf("Machine") !== -1) {
      machineSheets.push(sheets[i]);
    } else if (sheets[i].getName() === "Reports") {
      reportSheet = sheets[i];
    }
  }

  if (machineSheets.length > 0) {
    for (var j = 0; j < machineSheets.length; j++) {
      var sheet = machineSheets[j];
      var lastRow = sheet.getLastRow();
      for (var row = 2; row <= lastRow; row++) {
        if (sheet.getRange("B" + row).getValue() != "") {
          var cell = sheet.getRange("D" + row).getValue();
          var timestamp = new Date(cell);
          if (!isNaN(timestamp.getTime())) {
            if (sheet.getRange("I" + row).getValue() !== true) {
              var data = sheet.getRange("A" + row + ":G" + row).getValues();
              var duration = sheet.getRange("D" + row).getValue() - sheet.getRange("C" + row).getValue();
              dataRows.push(data[0]);
              sheet.getRange("I" + row).setValue(true);
              dataRows[dataRows.length-1].push(new Date());
              dataRows[dataRows.length-1].push("=D2:D-C2:C");
              dataRows[dataRows.length-1].push(sheet.getName());
            }
          } else {
            if (sheet.getRange("I" + row).getValue() !== false) {
              sheet.getRange("I" + row).setValue(false);
            }
          }
        }
      }
    }
    var range = reportSheet.getRange(reportSheet.getLastRow() + 1, 1, dataRows.length, dataRows[0].length);
    range.setValues(dataRows);
    var durationColumn = range.getColumn() + range.getNumColumns() - 1; //get the last column of the range
  }
}