Google sheets app script setting formula / value onEdit

Question

I have the following code to try and set a lookup formula when a user deletes a cell by mistake:

function onEdit(e)

{
  var ssA = SpreadsheetApp.getActive();
  var ss = ssA.getSheetByName("Completed Work Requests") 
  var lastRow = ss.getLastRow();
  var range = ss.getRange(5,6,lastRow,1);
  var data = range.getValues();


 for(var i=0;i<data.length;i++)
  {  
    if(data[i][0] == "")//If true then it's blank
    {
      data[i][0]="=IFERROR(INDEX('Client lookup sheet'!C[-1]:C[-1],MATCH(R[0]C[-4],'Client lookup sheet'!C[-5]:C[-5],false)),\"\")"
      range.setValue(data[i][0]);
    }
  }}

The problem I am having is the range.setValue(data[i][0]); part where I can see that it is setting the entire range to the value of data[i][0] in the previous line. The issue is that even if a user inputs a value manually, the onEdit function simply resets the value to data[i][0] = .... while I want them to be able to set the manual value without the function overwriting the value.

So my question boils down to what do I need to use instead of range.setValue(data[i][0]) to ensure only the cell evaluated by data[i][0] == "" is set to become a formula rather than the entire range?

Thanks!

Andy Andy · Accepted Answer · 2018-10-31T00:36:31

I have fixed my issues by editing my code to:

edit: The previous issue was that I couldn't figure out a way to set value when an instance of data[i][0] == "" was found. The original code in the question above had the setValue applied to the entire range so when a user manually inputted a value, the setValue would simply reset the value to the formula. I have found that I can find the range of data[i][0] using getRange(i+5,6) as the row starts at 5 and column number = 6. This new range can then be used to setValue at the appropriate cell rather than the whole range

function onEdit(e)

{
  var ssA = SpreadsheetApp.getActive();//changed from openById() for my convenience
  var ss = ssA.getSheetByName("Completed Work Requests") 
  var lastRow = ss.getLastRow();
  var range = ss.getRange(5,6,lastRow,1);
  var data = range.getValues();


 for(var i=0;i<data.length;i++)
  {  
    if(data[i][0] == "")//If true then it's blank
    {
      data[i][0]="=IFERROR(INDEX('Client lookup sheet'!C[-1]:C[-1],MATCH(R[0]C[-4],'Client lookup sheet'!C[-5]:C[-5],false)),\"\")"
      var rangedata = ss.getRange(i+5,6)
      rangedata.setValue(data[i][0]);
    }
  }}

Google sheets app script setting formula / value onEdit

1 Answers