I have a spreadsheet that is connected a Hubspot workflow. When a deal is closed in our CRM, Hubspot creates a new row on the sheet with 3 pieces of data from the deal. Due to Hubspot's recommended best practices for working with Google Sheets integration, I have another sheet where I reference data from the sheet that receives the data. The problem arose when I realized that the reference sheet would not automatically refresh when new data was written to the synced sheet. So, I created a macro that copies the formula down using the fill handle every 5 minutes. This rescans the synced sheet and writes any new data to the reference sheet. This morning I woke up and there was some new data that should have been copied over to the reference sheet -- however it wasn't. I checked that the macro is running every 5 minutes, and it is. For the hell of it, I went to the menu and manually ran the macro and, to my surprise, it actually worked! I want this to be fully automated though and don't want to have to manually run a macro everytime I want the data to get copied over.

Data gets synced here

Data is referenced here

Macro Code:

/** @OnlyCurrentDoc */

function RefreshData() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A75').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A75:A223'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('B75').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('B75:B234'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('C75').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('C75:C232'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('C75:C232').activate();
};

CodePudding user response：

The problem is that your macro, doesn't work in the time-driven triggers context. The following "macro" is able to be called by a time-driven trigger:

function RefreshDataByTimeDrivenTrigger() {
  var spreadsheet = SpreadsheetApp.getActive(); 
  spreadsheet.getRange('A75:A223')
    .setValue(spreadsheet.getRange('A75').getValue());  
  spreadsheet.getRange('B75:B234')
    .setValue(spreadsheet.getRange('B75').getValue());
  spreadsheet.getRange('C75:C232')
    .setValue(spreadsheet.getRange('C75').getValue()); 
};

Note:

1. /** @OnlyCurrentDoc */ was not included in the above code because it applies to the whole project.
2. I'm intentional using setValue instead of setValues as the same value should be added to all the cells from the specified range.

There are some methods that only works in certain contexts, i.e. SpreadsheetApp.getUi() works then called from a custom menu, but doesn't work when called from a time-driven trigger. The same happens with Range.activate(). Unfortunately the macro recorder adds a lot of Range.activate() and Spreadsheet.getActiveRange() methods and it's not smart enough yet to optimize the recorded user actions by itself, so, in order to make your macro to work when it's called by a time-driven trigger you have to adapt your code:

1. Remove statements like spreadsheet.getRange('A75').activate();.
2. Modify statements like spreadsheet.getActiveRange() to use statements like spreadsheet.getRange('A75').

If you are also interested in making your "macro" faster, you might also would like to replace patterns like

spreadsheet.getRange('A75').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A75:A223'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

by equivalent code that make fewer calls to Google Apps Script methods.

Related

• Google sheets macro won't run on trigger

CodePudding user response：

Try it this way:

function RefreshData() {
  var ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("SheetName");
  sh.getRange('A75').autoFill(sh.getRange('A75:A223'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  sh.getRange('B75').autoFill(sh.getRange('B75:B234'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  sh.getRange('C75').autoFill(sh.getRange('C75:C232'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
}