I have 2X2 table that changes with an ImportRange function.

Using the following line I can set a property from a single cell:

PropertiesService.getScriptProperties().setProperty("A2",SpreadsheetApp.getActive().getActiveSheet().getRange("A2").getValue());

How can I set a Property for a whole range (like A2:F100)? And then, how can I retrieve these properties values?

CodePudding user response：

I am giving below a onEdit function which uses the old value in the cell.

You can try something similar to capture if a performer is becoming under performer or vice versa

//
//
function onEdit(e) {
var activeSheet = e.source.getActiveSheet();
var oldValue;
var newValue;
var activeCell = activeSheet.getActiveCell();
if(  activeSheet.getName()=="Sheet1" && activeCell.getColumn()== 11 ) {
  newValue=e.value;
  oldValue=e.oldValue;
  if(!e.value) {
    activeCell.setValue("");
  }else {
    if (!e.oldValue) {
      activeCell.setValue(newValue);
    }else {
      if(oldValue.indexOf(newValue) <0) {
        activeCell.setValue(oldValue ',' newValue);
      }else {
        activeCell.setValue(oldValue);
      }
    }
  }
}
}
//

The above function makes a multi-select from a drop down - it adds values to a cell selected from a drop down (multiple items can be selected)

CodePudding user response：

function saveAll() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const vs = sh.getDataRange().getDisplayValues();
  PropertiesService.getScriptProperties().setProperty('All',JSON.stringify(vs));
}

function getAll(() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const vs = JSON.parse(PropertiesService.getScriptProperties().getProperty('All'))
  sh.getRange(1,1,vs.length,vs[0].length).setValues(vs);
}

This will only work on values that can be stringified. Values such as dates will not be returned as dates.