Functions in Google Sheets broken when script is run

Question

I'll start this off by saying I have no clue what I'm doing. I'm surviving off copying and pasting code off the internet for a spreadsheet me and my friends use for watching films together.

I've run into an issue where I'm updating a cell with the current date when another cell in that row is updated if its blank with a script. This issue is I then use a function in the cell next to it to give the difference in days for another date marked down in a cell (like a normal spreadsheet as that easier for me to do). But every time the script runs the function breaks and is replaced with the text "#NUM!" (Actually has that text as the function disappears from inside it).

I tried changing it to =U2 and that breaks also. Is this something that can't be done? The great almighty google god has not provided me with an answer so I've made an account here in hope of salvation.

tl;dr Scrips look like they are breaking my cell references for any sheet function that looks at cells they edit. How stop?

In cell V2 I have the function =DATEDIF(S2,U2,"D") Script bellow (I know not how to format)

function onEdit(event) {
var eventRange = event.range;
var sheetName = SpreadsheetApp.getActiveSheet().getSheetName();
if (sheetName == "Scores") {

  if (eventRange.getColumn() == 10) { //Check which is updated


    var columnXRange = SpreadsheetApp.getActive().getSheetByName("Scores").getRange(eventRange.getRow(), 21, eventRange.getNumRows(), 21);//where to write

    var values = columnXRange.getValues();

  for (var i = 0; i < values.length; i++) {
    if (!values[i][0]) {  // If cell isn't empty
     values[i][0] = new Date();
    }
  }
  columnXRange.setValues(values);
}
}  
}

Can you share a copy of your spreadsheet? It will be easier to help if we can see what is going on. Most likely the problem is that your two dates are formatted differently. One as a date value, and the other as a text value. — Ian Goodnight

Ian Goodnight Ian Goodnight · Accepted Answer · 2019-03-20T21:18:58

Ok, I see the problem. You are looking at a way bigger range than you want with

var columnXRange = SpreadsheetApp.getActive().getSheetByName("Scores").getRange(eventRange.getRow(), 21, eventRange.getNumRows(), 21);

You only really need the value of one cell to check if it is empty. Try replacing your function with :

function onEdit(event) {
  var eventRange = event.range;//makes shit happen?
  var sheetName = SpreadsheetApp.getActiveSheet().getSheetName();//checks current shit
  if (sheetName == "Scores") {//name of sheet want shit to happen

    if (eventRange.getColumn() == 10) { // 1 is column A, 2 is B ect

      // getRange(row, column, numRows, numColumns) sheet name to make not everywhere
      var columnXRange = SpreadsheetApp.getActive().getSheetByName("Scores").getRange(eventRange.getRow(), 21, 1, 1);//num is where will write 1 is a ect

      var values = columnXRange.getValues();//takes all shit from above to use as range

      if (!values[0][0]) {  // If cell isn't empty
         values[0][0] = new Date();//set date to the vaules in the range
      }

      columnXRange.setValues(values); //use the values set above and write them in 
    }
  }  
}

..and that should fix your problem. The problem with your current script is that the script is copying the "value" of your column v cells and replacing it with just a text value. This limits the range you are grabbing to just the cell you need, eliminates the for() loop, and steps over the problem entirely.

Functions in Google Sheets broken when script is run

1 Answers