Referencing cells on another sheet through appscript-CodePudding

Using google sheets, I'm trying to reference a cell on another spreadsheet (called setup). The cell will format if the data on the cell B2 on the sheet named setup.

  conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getRange('B2')])
  .whenTextContains('setup!B2')
  .setBackground('#B7E1CD')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);

This doesn't seem to be working. Is there any way to reference cell B2 in the setup sheet (this is what I did .whenTextContains('setup!B2'))

Thanks

CodePudding user response：

.whenTextContains() requires a String, when using your current code the formatting rule will apply if the values are specifically "setup!B2", if you want the values in cell B2 at sheet setup you'll need to use a different method:

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("setup").getRange("B2").getValue();

You can try this modification:

  conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  var newValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("setup").getRange("B2").getValue();
  conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getRange('B2')])
  .whenTextContains(newValue)
  .setBackground('#B7E1CD')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);

Resources:

Range.getValue()

CodePudding user response：

Alternative:

function setBGonFormula() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var cell = spreadsheet.getRange('B2');
  var formula = cell.getFormula(); 
  if(formula == '=Setup!B2'){
    cell.setBackground('#B7E1CD');
  }

}

This is a sample implementation if you aim to get the cell formula as a condition to set the background colour of your choice.

Reference:

getFormula()