Cell coloring in google-spreadsheet fails if it is called from a cell, but works ok when called from the script.

Question

I have created the following simple function:

function test(r,c) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(r,c).setBackground("red");
  return 1;
}

---

In the spreadsheet, I write "=test(row(),column())

This results in ERROR with the following message: Error: You do not have permission to call setBackground (line 3).

It is NO problem if I create another function call in the script as follows:

function test_the_test(){
  test(5,4); 
}

Why can't I call the test function from the spreadsheet cell?

Thank you in advance

Wallow

Answer 1

As it is clearly explained in the documentation, Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. That is of course also true for other methods such as setBackground etc.

Combat

Answer 2

It's not possible to call anything which sets content from cell, but it is possible to call it from buttons.

Actually is quite easy. Definitely it's not true that you can't change others cell content.

The trick is to not call the function from cell but mount it into drawing/image.

1. Click insert -> drawing
2. Draw a image and save it (you should see your image in spreadsheet)
3. Click it by right mouse button - in top right corner, there is a little triangle opening options
4. Click Assign script and type name of your script without parenthesis (like "test" not "test()" ) and confirm
5. Click the button. A pop-up window asking for privileges to access spreadsheet appears.
6. Confirm it, if problem with refresh occurs just refresh it manually (F5)
7. Now you can click the button and you can edit any cell you like

This code work fine when mounted to button.

function test() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(3,5).setBackground("red");
}

Unclassical

Answer 3

The current version of Google Sheets (Jan 2022) uses the Apps Script editor and allows you to put scripts in a container file that automatically attaches to your Google Sheet.

You can open the Apps Script editor from the Google Sheets menu under Extensions -> App Script

In the default Code.gs editor file you can simply extend a default event hook such as onEdit() and put in logic to filter your actions to a specific set of circumstances. Here is a simple example of using this hook:

function onEdit(e) {
  if( ! e ){
    return;
  }
  var currentSheet = e.source.getActiveSheet();
  var currentRange = e.range;
  // only want action to occur when a single cell changes
  if( currentRange.getNumRows() === 1 && currentRange.getNumColumns() === 1 ){
    var currentColumn = currentRange.getLastColumn();
    var currentRow = currentRange.getLastRow();
    // only want action to occur for a column on a certain sheet
    var myTargetSheet = "Sheet 1";
    var myTargetColumn = 1;
    if( currentSheet.getName() == myTargetSheet && currentColumn == myTargetColumn ){
      // set background color for the selected row based on a lookup
      var cellValue = currentRange.getCell(1,1).getValue();
      var assignedColor = myCustomSearch( cellValue );
      currentSheet.getRange( "A" + currentRow + ":E" + currentRow ).setBackgroundColor( assignedColor );
    }
  }
}

function myCustomSearch( searchTerm ){
  var assignedColor = "#ffffff";
  var lookupSheet = SpreadsheetApp.getActive().getSheetByName("Sheet 2");
  var lookupRange = lookupSheet.getRange("H1:H20"); // where the search terms live
  var numColumns = lookupRange.getNumColumns();
  var numRows = lookupRange.getNumRows();
  var lookupList = lookupRange.getValues();
  for( var myColumn=0; myColumn < numColumns; myColumn++ ){
    for( var myRow=0; myRow < numRows; myRow++ ){
      if( lookupList[myRow][myColumn] == searchTerm ){
        assignedColor = lookupSheet.getRange("H"+(myRow+1)).getBackgroundColor();
        break;
      }
    }
  }
  return assignedColor;
}

Homey