I am looking for a simple script which can turn this

into this

As you can see I have manually copied and pasted those empty cells in E column with the corresponging value from the A column: I need a script that can do this automatically.

I guess it needs building two different sets of data from two different arrays, and then comparing them, but unfortunately I don't have any idea how to perform that ..

  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var sheet     = ss.getSheetByName('name');
  var lastRow   = sheet.getLastRow();
  var extension = lastRow - 1;
  
  var colA = sheet.getRange(1, 1, extension).getValues();
  var colE = sheet.getRange(1, 5, extension).getValues();
..
  }

CodePudding user response：

Assuming your objective is as follows:

When column (fine)(F) is empty, fill it with the value of column data(A).

The steps are:

1. Get the values of your data.
2. Check which rows have a length less than 5
3. Fill that one with the data from column A.

Sample:

Code.gs

const sS = SpreadsheetApp.getActiveSheet()
function fillEmpty() {
  /* Getting the data from the sS */
  const range = sS.getRange('A2:E'   sS.getLastRow())
  const values = range.getValues()
  /* Tmp Values */
  let tempValues = []
  values.forEach((row, idx) => {
    /* Check the length of the array */
    const check = row[4]

    /* UPDATED TO CHECK ONLY IF THE `fine` exists */
    if(check){
      tempValues.push(row)
    } else {
      /* Change the color for the modified */
      sS.getRange(idx   2, 5).setBackground('#ffee00')
      let tmpR = row
      tmpR[4] = tmpR[0]
      tempValues.push(tmpR)
    }
  })
  range.setValues(tempValues)
}

From this:

To this:

Documentation

• setValues(Object[][])
• Array.prototype.fill()