Google-sheets – Change certain cells back to default value every Monday morning in Google Sheets

google sheets

I have a Google spreadsheet that ~30 members can edit (one row for each member). I need to create a column showing "yes" as default in each row until the member manually changes it to "no" if he/she considers applicable. Every Monday morning at 6 am, I need this column to automatically change back to default "yes". Anyone can help with the code?

Best Answer

Assuming the column is B for the purpose of this example, here's a function that fills it with "yes":

function yes() {
  var ss = SpreadsheetApp.openById( ... );  // see below
  var sheet = ss.getSheetByName("Sheet1");  // the name of the sheet
  var range = sheet.getRange("B1:B");       // or another column
  var numRows = range.getHeight();
  var values = [];   
  for (var i=0; i<numRows; i++) {
    values.push(["yes"]);
  }
  range.setValues(values);  
}

The id of a spreadsheet is in its URL: /spreadsheets/d/.../edit

To execute this function daily, go to Resources > Current projects' triggers, create a daily trigger and specify its running time.

Related Topic