0

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.Picture of sheet

1
  • Welcome. Yes, this can be done automatically with a script. Do you have skills with scripting?. As a guide, you might look at Copy and Paste Cell Contents Once a Week. This is 90% of what you want to do; the only difference is that this script copies the content to an adjacent cell, whereas you want to copy to the next blank cell. – Tedinoz Dec 13 '20 at 1:42
0

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

trigger

1
  • This is perfect. Thanks a ton. – RamB Dec 14 '20 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.