Google Sheets code to copy formulas from a row and paste into the next empty row and then remove for-CodePudding

I have a Google Sheets spreadsheet that I use to record data from Google Finance on a daily basis. I would like some help with code to do the following (I'll use the fields from the linked document):

Copy the formulas from the last populated row(2698 A:H) into the next blank row(2699 A:H).
Remove the formulas from the previous entry row(2698 A:H) but keep the results of those formulas in the cells.
I want to be able to do this each day. The previous day's data for the formulas is available each work day (minus holidays) by 9am EST. I'm not sure the best way to trigger the code to perform this task. I'm open to suggestions.

I have formulas on the last row of tab "Historical_Data".

A(Date):=workday(A2697,1,'NYSE Holidays'!$A$2:$A$27) <used for next work day minus the holidays, on tab "NYSE Holidays"> B(VIX9D):=INDEX(GOOGLEFINANCE("INDEXCBOE:VIX9D","close",$A2698),2,2) C(VIX1D):=INDEX(GOOGLEFINANCE("INDEXCBOE:VIX","close",$A2698),2,2) D(VIX3M):=index(GOOGLEFINANCE("INDEXCBOE:VIX3M","close",$A2698),2,2) E(VIX6M):=index(GOOGLEFINANCE("INDEXCBOE:VIX6M","close",$A2698),2,2) F(VIX1Y):=index(GOOGLEFINANCE("INDEXCBOE:VIX1Y","close",$A2698),2,2) G(VVIX):=index(GOOGLEFINANCE("INDEXCBOE:VVIX","close",$A2698),2,2) H(SPX):=index(GOOGLEFINANCE("INDEXSP:.INX","close",$A2698),2,2)

CodePudding user response：

Put a daily trigger on this function at the right time according to the availability of data

// Record history from a cell and append to next available row
function recordValue() {
  if (isNotHoliday()){
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Historical_Data");
    var lastRow = sheet.getLastRow();
    var oldDate = sheet.getRange(lastRow,1).getValue();
    var rng = sheet.getRange(lastRow,1,1,8);
    rng.copyTo(sheet.getRange(lastRow 1,1,1,8));
    rng.setValues(rng.getValues());
  }
}

function isNotHoliday(){
  var yesterday = new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()-1);
  var formattedDate = Utilities.formatDate(yesterday, Session.getScriptTimeZone(), "M/d/yy")
  var holidays = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('NYSE Holidays').getRange('A2:A').getDisplayValues().join().split(",");
  return (! ~holidays.indexOf(formattedDate))
}

CodePudding user response：

Even though the answer provided here seems to solve this in terms of the programmatic approach, I want to add the following pieces of information that might help in the long term as well.

Since you are using Google Sheets, Apps Script is indeed the right choice for your task.

Moreover, since you want to execute the function at a specific time each day, the best solution would be to use a time-based trigger. According to the official documentation, time-driven triggers let scripts execute at a particular time or on a recurring interval. However, the time might be slightly randomized — for example, if you create a recurring 9 a.m. trigger, Apps Script chooses a time between 9 a.m. and 10 a.m., then keeps that timing consistent from day to day so that 24 hours elapse before the trigger fires again.

To create a time based trigger, you can use the following function:

function createTimeDrivenTrigger() {
  ScriptApp.newTrigger('FUNCTION_THAT_COPIES_THE_ROWS')
      .timeBased()
      .atHour(9)
      .create();
}

As for copying the specific rows, the following methods might be of help to you:

getRange - which is used to retrieve the range in which the values can be found;
getValues - which is used to retrieve the values from the specified range;
setValues - which is used to place the values to the specified range;
getFormulas - which is used to retrieve the formulas from a specified range;
setFormulas - which is used to place the formula to the specified range.

Reference