CODEWITHSUNDEEP
google-apps-script
Jéjé
Jéjé

Reputation: 11

A script in Google spreadsheet works from the script control windows but not from a trigger in the sheet?

I have a problem with this little code I have built for highlighting a word in a Google Sheet.
This code works perfectly when I launch it from the script control windows but it doesn't work when I try to trigger the script from a cell in the sheet.
I'm the owner of this google spreadsheet. Here is the code :

function Surligner(nom) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
  var mois = sheet.getRange("A4").getValue().getMonth() ;
  var listeNbrJours = new Array(31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31) ;

  // nom = sheet.getRange("M1").getValue() ;

  sheet.getRange(4, 5, listeNbrJours[mois],9).setBorder(true, true, true, true, true, true, "black", null ) ;
  sheet.getRange(4, 5, listeNbrJours[mois],9).setFontColor("black") ;

  for (var i=4 ; i<listeNbrJours[mois]+3 ; i++)
  {
    for (var j=5 ; j<14 ; j++) 
    {
      if (sheet.getRange(i,j).getValue().match(nom) ) 
      { 
        sheet.getRange(i,j).setBorder(true, true, true, true, true, true, "red", null ) ;
        sheet.getRange(i,j).setFontColor("red") ;
      }
    }
  }
}

I trigger the script from the sheet with =SURLIGNER(M1) where M1 is the cell who contain the name to highlight.
The message error is :

you are not allowed to call the setBorder (line 9)

sheet

Upvotes: 1

Views: 60

Answers (1)

sudo bangbang
sudo bangbang

Reputation: 28169

Apparently you can't use a custom function to run set* functions

Quoting from Custom Functions in Google Sheets docs

Spreadsheet Read only (can use most get*() methods, but not set*()).

This means calling the function from sheet won't work.

One possible work around will be to use this

nom = sheet.getRange("M1").getValue() ; // I think you thought about it too

Then run Surligner it using onEdit event trigger (although it might create problems due to expensive Range.getValue method.

Upvotes: 1

Related Questions