Run Google Apps Script from menu manually and not onOpen

1
votes

I've recently updated my script to call this from a custom menu rather than a button in the sheet but what I've found is the script runs the 'copyNew' function immediately on open, but I'd like this to only run when I manually click this from the custom menu, can anyone direct me on adapting this script to achieve this, please?

The 'copyNew' function copies everything from the 'Results' sheet on to a 'NEW' sheet The 'clearCells' function clears certain columns from the 'Results' sheet

This is the script:

function onOpen() {

var ui = SpreadsheetApp.getUi()
  ui.createMenu("Payroll")
    .addItem("Create New Month", "CopyNew")
    .addItem("Reset Results", "ClearCells")
    .addToUi();
}

function CopyNew() {  
  var ss = SpreadsheetApp.getActive();
  var sheetName = ss.getSheetByName("Results");
    var createSheet = ss.getSheetByName("NEW");
    if(!newSheet){
      var createSheet = ss.insertSheet("NEW");
      var sourceRange = sheetName.getRange("A1:Q100");
      var targetRange = newSheet.getRange("A1:Q100");
      targetRange.setValues(sourceRange.getValues());
      sourceRange.copyTo(targetRange, {formatOnly:true});
    }
}

CopyNew();//remove this
function ClearCells() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('H7:H100').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('L7:L100').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('N7:N100').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('Q7:Q100').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

Thanks!

1
javascriptgoogle-apps-scriptgoogle-sheetstriggers
Just remove the the CopyNew(); That will cause copyNew() to be run every time any function is accessed including onOpen(). There's nothing wrong with onOpen() - Cooper
That's it, thank you very much - Truss

1 Answers

1
votes

Just like this:

function onOpen() {

var ui = SpreadsheetApp.getUi()
  ui.createMenu("Payroll")
    .addItem("Create New Month", "CopyNew")
    .addItem("Reset Results", "ClearCells")
    .addToUi();
}

function CopyNew() {  
  var ss = SpreadsheetApp.getActive();
  var sheetName = ss.getSheetByName("Results");
    var createSheet = ss.getSheetByName("NEW");
    if(!newSheet){
      var createSheet = ss.insertSheet("NEW");
      var sourceRange = sheetName.getRange("A1:Q100");
      var targetRange = newSheet.getRange("A1:Q100");
      targetRange.setValues(sourceRange.getValues());
      sourceRange.copyTo(targetRange, {formatOnly:true});
    }
}

function ClearCells() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('H7:H100').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('L7:L100').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('N7:N100').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('Q7:Q100').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
}