0

So i have a google form that every time a new response comes into the spreadsheet, i want the formula in cell A1 to copy down and fill the A column down to the bottom of the sheet. Recording a macro to do this is easy enough (below), but getting it to run every time the sheet is changed is beyond me.

function UpdateSheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A2').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A2:A166'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('A2:A166').activate();
};

So either the macro runs automatically every 30 seconds or so, or it runs when the sheet is modified. Any help would be most welcome.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Alasdair O'Connor
  • 11
  • 2
  • 2
    Did you check out events (see [Google Apps Script - Event Objects](https://developers.google.com/apps-script/guides/triggers/events))? Also see [Google Spreadsheet Script onChange event not firing](https://stackoverflow.com/questions/22462699/google-spreadsheet-script-onchange-event-not-firing) – Pᴇʜ Sep 18 '20 at 07:43
  • You'd use the ``onFormSubmit`` event or alternatively arrayformulas. See https://stackoverflow.com/q/63837444 – TheMaster Sep 18 '20 at 21:45

0 Answers0