Google Script - Update/find&replace references in copied sheet

Question

I made a script that copies the last sheet of a spreadsheet and assigns it the current date as the name, i.e. "July 28, 2019".

Now, there are dozens of formulas in those sheets that reference the date before it for calculationsand I need them to auto-update as well as changing all the references by hand is too tedious of a process. There are over 80 of them.

In Excel, I've been able to solve this issue by adding a function that replaced every occurrence of the sheet from two days ago to the sheet from once day ago simply by comparing names in the given data ranges via the Replace method in VBA. Now I need to do the same in google script, but I can't find a similar method in the Google Script documentation.

Does anyone know how to go about doing this? Googling the issue didn't turn up much help. I've included the code I use in VBA below for clarification purposes.

Public Sub FormulaUpdate()

to_replace = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.count - 2).name
replace_val = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.count - 1).name

range("D2:D100").Replace What:=to_replace, Replacement:=replace_val, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

range("G45:G54").Replace What:=to_replace, Replacement:=replace_val, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

End Sub

Would you please share a copy of your spreadsheet, remove any private or confidential information; and also include an example of a successful outcome Would you also edit your question to include the script that you have written, and any errors that are reported when you run it. Lastly, would you edit your question to include an example of one of the formula that you wish to change/update. Thank you — Tedinoz
I'm not sure if I understood. You want to replace the dates from two days ago, to yesterday? — Jescanellas
@Jescanellas Yes, as the spreadsheet/workbook is a series of daily records, each day having it's own sheet and comparing and calculating against entries in the previous date's sheet. — Arcturus

Jescanellas Jescanellas · Accepted Answer · 2019-07-30T13:24:47

Try this code:

lastrow gets the last row with data of A (I used this column as example, all the dates are stored there), while col_A gets the data from A2 (A1 is the header) to the last row we got before.

function main() {

  var sprsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sprsheet.getActiveSheet();
  var lastrow = "A" + (sheet.getLastRow());
  var col_A = sheet.getRange('A2:'+lastrow).getValues();

  var yesterday = getYesterday();
  var twoDaysAgo = deduceTwodays();

  for (var i = 0; i < col_A.length;i++){
    if (col_A[i] == twoDaysAgo){
      sheet.getRange("A"+(i+2)).setValue(yesterday);
    }
  }
}

These are very simple functions. Date() works with milliseconds, so we have to convert those 24 and 48 hours to this unit. Then we format the result with the one you need: "July 28, 2019" equals 'MMMM dd, yyyy". See more info about date formatting here.

function getYesterday() {
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24; //24 hours
  var now = new Date();

  var formattedDate = Utilities.formatDate(new Date(now.getTime() - MILLIS_PER_DAY), "GMT", "MMMM , dd, yyyy");
  return formattedDate;
}
function deduceTwodays() {
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 48; //48 hours
  var now = new Date();

  var formattedDate = Utilities.formatDate(new Date(now.getTime() - MILLIS_PER_DAY), "GMT", "MMMM , dd, yyyy");
  return formattedDate;
}

Note: The dates in the cells must be stored in plain text format, otherwise the comparing condition will fail as it will read an entire date with it's time.

Google Script - Update/find&replace references in copied sheet

1 Answers