CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user10531291
user10531291

Reputation:

onOpen if column B is blank fill column A with todays date

I have a spreadsheet where there is a value in column B and in column A is inserted a date, and i want to create a script that when the spreadsheet is open, inside the function onOpen (){}, checks if there is a cell in column B filled but left with column A (date) blank, then automatically inserts todays date, this needs to be done via app script, can anyone give some light?

Timesheet Example

Upvotes: 0

Views: 152

Answers (1)

Cooper
Cooper

Reputation: 64042

If columnB and not Column A then insert Date into column A

function onOpen() {
  ifBAndNotA();
}

function ifBAndNotA() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");//change sheet name as required
  const sr = 2;//data startrow;
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, 2).getValues();
  vs.forEach((r, i) => {
    if (!r[0] && r[1]) {
      sh.getRange(i + sr, 1).setValue(new Date()).setNumberFormat("dd/MM/yyyy");
    }
  });
}

Upvotes: 1

Related Questions