Google Sheets script- Copy and paste data from fixed cell to a row corresponding to a cell with current week date

Question

I have a Google Sheets document where I track status of a project as red/yellow green. the live count of Red/Yellow/Green are in Cells BU1 BV1 and BW1 of my summary Sheet. Now I have a table below in the same sheet with a list of dates (Mondays) in column BQ. Currently I go in every Monday and copy and paste the data in the row corresponding to this weeks Monday date in order to maintain history. Is there a script which would do this automatically ever Monday or a script which would auto populate on execution.

Answer 1

You want to update a new row of data each Monday.

You need to do two things:

• create this script in the script editor for your spreadsheet.
• create a time-driven trigger that will automatically execute the script every monday at a given time.

---

function wa_14922101() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // define the sheet to execute this script
  var sheetname = "<<substitute your sheet name>>";
  var sheet = ss.getSheetByName(sheetname)

  // get the number of rows of data in column BU
  var BUvals = sheet.getRange("BU4:BU").getValues();
  var BUlast = BUvals.filter(String).length;
  var BULR = BUlast+3;
  // Logger.log("DEBUG: the number of records is "+BUlast+", so the last row = "+BULR);

  // define the source range and get the values
  // Column BU = Column 73
  var sourcerange = sheet.getRange(1, 73, 1, 3);
  //Logger.log("DEBUG: the source range is "+sourcerange.getA1Notation());
  var sourcedata = sourcerange.getValues();
  
  // define the target range
  var targetrange = sheet.getRange(BULR+1, 73, 1, 3);
  // Logger.log("DEBUG: the target range is "+targetrange.getA1Notation());
  
  // copy the data in row#1 to the next row.
  targetrange.setValues(sourcedata);
  
  return false;
}

---

Trigger settings