In Google Sheets, why does my recorded macro also run two other scripts when invoked?

Question

I have recorded a macro in Sheets, the purpose of which is to add a colour scale with conditional formatting. It works fine, but I get two popups saying "Done" when it completes. I have traced these to two other app scripts I have used in the past. (Note: These are scripts , not recorded macros)

Q. Why, when I invoke the macro with its assigned keyboard shortcut, do I also get the popups for the other scripts? The scripts themselves don't appear to run fully (as the ranges they amend are unchanged).

This is the recorded macro:

function Addcolourscale() {
  var spreadsheet = SpreadsheetApp.getActive();
  var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .whenCellNotEmpty()
  .setBackground('#B7E1CD')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
  conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .setGradientMinpoint('#57BB8A')
  .setGradientMaxpoint('#FFFFFF')
  .build()); 
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
  conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .setGradientMinpoint('#57BB8A')
  .setGradientMidpointWithValue('#FFD666', SpreadsheetApp.InterpolationType.PERCENTILE, '50')
  .setGradientMaxpoint('#E67C73')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
};

This is one of the scripts that produces the popup, due to its final two lines, when the above macro completes. It's something I adapted from an online resource, so I don't know it well.

function CopyClientChannelUseBack(){
    /* Edit the vars below this line for your needs */
    var sourceSheet  = "12 mth Client channel use" ;  // Enter the name of the sheet with the source data
    var sourceRange = "A19:S29" ; // Enter the range of the cells with the source data
    var targetSheet = "12 mth Client channel use" ; // Enter the name of the target sheet  
    var targetRange = "A20:S30" ; // Enter the range of cells you wish to copy data to. Note this must be same size as source range.
    /* No need to edit below this point */  


    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sourceSheet);
    var values = sheet.getRange(sourceRange).getValues();
    ss.getSheetByName(targetSheet).getRange(targetRange).setValues(values);
  SpreadsheetApp.flush()

      /* Edit the vars below this line for your needs */
    var sourceSheet  = "12 mth Client channel use" ;  // Enter the name of the sheet with the source data
    var sourceRange = "A49:N59" ; // Enter the range of the cells with the source data
    var targetSheet = "12 mth Client channel use" ; // Enter the name of the target sheet  
    var targetRange = "A50:N60" ; // Enter the range of cells you wish to copy data to. Note this must be same size as source range.
    /* No need to edit below this point */ 

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sourceSheet);
    var values = sheet.getRange(sourceRange).getValues();
    ss.getSheetByName(targetSheet).getRange(targetRange).setValues(values);
  SpreadsheetApp.flush()      
  SpreadsheetApp.getUi().alert("Done")
}

The manifest file has no reference to the scripts, only the macro.

{
  "timeZone": "Europe/London",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "sheets": {
    "macros": [{
      "menuName": "Add colour scale",
      "functionName": "Addcolourscale",
      "defaultShortcut": "Ctrl+Alt+Shift+9"
    }]
  }
}

AFAIK, no. I don't know what a trigger is, so I haven't added one. Aha, I just found Edit > Current project's triggers and Edit > All your triggers. Both say 'no triggers have been set up' — Gary Miller
If you remove those two lines, Do you still get a popup, when macro is run? — TheMaster
@I'-'I note that if this is a shared sheet, the triggers could be installed by a different user, and OP cannot find out about them. — tehhowch

Gary Miller Gary Miller · Accepted Answer · 2018-09-17T07:29:22

OK. I worked out what the problem was. Nothing to do with the code I pasted. It turned out that I had added some code on the end of the other scripts, literally on the end, outside of the closing } bracket of that function. so those few lines were running as well. It appears GAS runs through all the scripts available, looking for the one that's just been called. As there was code outside of the functions, that was being executed too.

In Google Sheets, why does my recorded macro also run two other scripts when invoked?

1 Answers