Google Scripts onEdit to trigger another function upon selection change in drop down menu

user_17368433 0
Viewed 594

I have a working function set up which searches another tab for a match from the selection in a drop down menu, and then copies the background/font colours from a specified grid below the match and pastes it onto a grid in the main tab. The script is as follows:

    function cellColours() {

    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var sheetRanges = ss.getSheets()[2]
    var sheetCalculator = ss.getSheets()[0]

    var dropdownMenu = sheetCalculator.getRange("F2")
    var dropdownValue = dropdownMenu.getValue()
    var textFinder = sheetRanges.createTextFinder(dropdownValue)
    var firstOccurrence = textFinder.findNext()

    var startCell = firstOccurrence.offset(2, 0)
    var endCell = startCell.offset(12, 12)

    var rangesGrid = 
    sheetRanges.getRange(startCell.getA1Notation()+':'+endCell.getA1Notation())
    var cellColours = rangesGrid.getBackgrounds()
    var textColours = rangesGrid.getFontColorObjects()

    var calculatorGrid = ss.getSheets()[0].getRange("F4:R16")
    var cellColourPaste = calculatorGrid.setBackgrounds(cellColours)
    var textColourPaste = calculatorGrid.setFontColorObjects(textColours)
    }

What I would like to happen is for the script to be automatically run whenever the selection in the dropdown menu (cell F2) is changed - have been messing around with onEdit to no avail! Is there a way for telling the on edit to run a different script when this menu is changed?

I have also tried incorporating the function into onEdit like this:

function onEdit(e) {
var range = e.range;
var spreadsheet = e.source;
var sheetName = spreadsheet.getActiveSheet().getName();
var column = range.getcolumn();
var row = range.getrow();

var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheetRanges = ss.getSheets()[2]
var sheetCalculator = ss.getSheets()[0]

var dropdownMenu = sheetCalculator.getRange("F2")
var dropdownValue = dropdownMenu.getValue()
var textFinder = sheetRanges.createTextFinder(dropdownValue)
var firstOccurrence = textFinder.findNext()

var startCell = firstOccurrence.offset(2, 0)
var endCell = startCell.offset(12, 12)

var rangesGrid = sheetRanges.getRange(startCell.getA1Notation()+':'+endCell.getA1Notation())

if(sheetName == 'Calculator' && column == 6 && row == 2)
{SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Shoving 
Ranges').getRange(rangesGrid).getBackgrounds()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Shoving 
Ranges').getRange(rangesGrid).getFontColorObjects()
 SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Calculator').getRange("F4:R16") 
 .setBackgrounds().setFontColorObjects()
 }
  }

Any help would be much appreciated! I'm very new to scripting and while banging my head against the wall until I find something that works has got me this far I'm afraid I've run out of surfaces to slap for this last part!

Thanks Feargus

google-apps-script google-sheets eventtrigger
1 Answers

What you need to do is evaluate e before doing the rest of the work. Set your onEdit function to the following, which confirms if the cell that was edited is the dropdown menu and then, if it was, runs the cellColours() function.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetCalculator = ss.getSheets()[0]
  var dropdownMenu = sheetCalculator.getRange("F2").getA1Notation
  
  var cell = e.range.getA1Notation;
  if (cell = dropdownMenu) {
    cellColours();
  }
}

Also, I noticed the last two lines of your original function declare variables but you can just call the methods unless you need to use:

    calculatorGrid.setBackgrounds(cellColours)
    calculatorGrid.setFontColorObjects(textColours)
user_94822890
Related