Fill range with string only if cell is empty and another sibling cell is not empty-CodePudding

I have a table, with columns start and finish and I'd like fill cells in finish column automatically with 0 only if finish cell is empty and start cell next to it not empty

So far I have this script that supposed to fire when a cell in start column (A3:A range) selected:

function onSelectionChange(e)
{
  const range = e.range,
        sheet = e.source.getActiveSheet(),
        col = range.getColumn(),
        row = range.getRow(),
        columns = [1], //check only first column (A)
        startRow = 3; //range A3:A
//only allow single cell selection starting at A3:A
  if (row < startRow || !columns.includes(col) || range.getNumColumns() !== 1 || range.getNumRows() !== 1 || sheet.getName() !== "Sheet1")
    return;

  for(let i = 0; i < columns.length; i  )
  {
    const start = sheet.getRange(startRow, columns[i], 10, 1), //get 10 rows
          finish = sheet.getRange(startRow, columns[i]   1, 10, 1); //get 10 rows

      if (!start.isBlank())
      {
        if (finish.isBlank())
          finish.setValue(0);

      }
  }
}

Unfortunately this fills EVERY cell in finish column and only when ANY cell in start column not empty and EVERY cell in finish column is empty

Do I have to loop through each cell individually to achieve this?

CodePudding user response：

For updating the single row one by one.

Try below sample script:-

function onEdit(e)
{
  const range = e.range;
  const sheet = range.getSheet();
  const row = range.getRow();
  const column = range.getColumn(); 
  if(column === 1)
  {
    const finsh_Column =  range.offset(0,1);
    if(finsh_Column.getValue().length === 0)
    {
      finsh_Column.setValue(0)  
    }
  }
}

CodePudding user response：

If you don't mind to process all the rows at once it could be something like this:

function onSelectionChange(e) {
  if (e.range.columnStart == 1 && e.range.rowStart > 3) add_zeros();
}

function add_zeros() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('A3:B'   sheet.getLastRow());
  var data = range.getValues();

  data.forEach(row => row[1] = (row[0] != '' && row[1] == '') ? 0 : row[1]);

  range.setValues(data);
}