0

I use the following code for an appscript

function onEdit(e) {
  var range = e.range;
  var reportSheet = range.getSheet();
  if (reportSheet.getSheetName() != "CHRankingsRohdaten" || range.rowStart != 1 || range.columnStart != 1) return;
  var ss = e.source;
  var recordsSheet = ss.getSheetByName("CHDatabase")
  var saveData = reportSheet.getRange("A1:F").getValues();
  var lastRow = recordsSheet.getLastRow();
  recordsSheet.getRange(lastRow + 1, 1, saveData.length, 6).setValues(saveData);
}

The values that triggers the onEdit() function are loaded from another sheet by =query(importrange( function and this does not seem to work, because the trigger is not fired. Only if I enter data by hand, then the trigger is fired. How can I run the onEdit() function with the imported data from the =query(importrange( function?

Rubén
  • 34,714
  • 9
  • 70
  • 166
user3392296
  • 644
  • 4
  • 16
  • you cannot. onEdit() will only run based on hand-entered data – MattKing Jan 22 '22 at 16:14
  • 1
    it is very possible, however, to skip the importrange altogether, and directly import and save the data to the CHDatabase only when the source data changes. But you have not shared a sample sheet so it will be difficult to demonstrate how to do that. – MattKing Jan 22 '22 at 16:16
  • Thanks. Is there a way to use =query(importrange( without overwriting new data imported? Somekind of append function? – user3392296 Jan 22 '22 at 16:25
  • The previous comment is a complete different question, anyway the answer is no,but it's very possible, however... if you follow the MattKing advise. – Rubén Jan 22 '22 at 16:55
  • Yes. There is an append function for just such a scenario. You just have to install it! – MattKing Jan 22 '22 at 22:35
  • Can you share what the QUERY() is? what is the exact formula? – MattKing Jan 23 '22 at 16:26

1 Answers1

0

That is the way that edit, simple and installable, triggers works, but the installable change trigger is triggered when a importrange changes its results with the event property changeType value equal to 'OTHER' (I only tested this using "closed "references like A1:A10 not with "open" references like A:A.

In order to be able to use the change trigger you should change a bit your script

  1. Change the function name

  2. Instead of

    var range = e.range;
var reportSheet = range.getSheet();

    try

    var reportSheet = SpreadsheetApp.getActiveSheet();

    This might work under certain circumstances. If it doesn't you might have to use a "witness" cell or range to detect if the formula result was updated.

  3. You should change this line if (reportSheet.getSheetName() != "CHRankingsRohdaten" || range.rowStart != 1 || range.columnStart != 1) return; (there aren't enough details to suggest something without guessing several things)

1 as a good practice (use reserved functions name only for the corresponding trigger).
2 and 3 are because change event object doesn't include range property.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Thanks Rubèn. I've tried this but it is not working function onChange(e) { var reportSheet = SpreadsheetApp.getActiveSheet(); if (reportSheet.getSheetName() != "CHRankingsRohdaten!A1") return; var ss = e.source; var recordsSheet = ss.getSheetByName("CHDatabase") var saveData = reportSheet.getRange("A1:F").getValues(); var lastRow = recordsSheet.getLastRow(); recordsSheet.getRange(lastRow + 1, 1, saveData.length, 6).setValues(saveData); } – user3392296 Jan 23 '22 at 07:46
  • @user3392296 please provide more details in your question so others might reproduce your case like if formula using the IMPORTRANGE is in the default sheet, the first sheet or another sheet, how the exporting and importing spreadsheets are used (are they shared or you are the only user, etc.) P.S. I made a slight change to this answer. – Rubén Jan 23 '22 at 16:53
  • `SpreadsheetApp.getActiveSheet()` currently does not work `onChange` because of a [bug](https://issuetracker.google.com/183511657). So if you want to use the workaround with `onChange`, you need to remove the `if (reportSheet.getSheetName() != "CHRankingsRohdaten"...)` condition and let your code execute regardless of the active sheet. What you can do to avoid unnecessary executions is to query for `changeType` [(EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER)](https://developers.google.com/apps-script/guides/triggers/events#change). – ziganotschka Jan 24 '22 at 09:14