Automatic trigger not working when new data imported through script in other spreadsheet

Question

Problem

I have the following script. What it does when triggered manually is :

• Creating new sheets with the name of each person in column A
• Adding a row in the newly created sheet with the data of columns A and B
• Other stuff with no impact on the problem below

When I set the built-in trigger, it simply doesn't work.

What I already tested

What is important to know in my opinion is that the data in column A and B in this spreadsheet is imported thanks to another script in another spreadsheet. Why is it important ? Because if I set a built-in trigger on change or edit and I add a row manually, it triggers my built-in trigger as perfectly as when I trigger it manually.

Conditions

Last thing to explain, I don't want a "solution" that forces me to make a manual action to make it work. I need it to work automatically, not with some kind of checkbox or something.

Does anyone could have a solution for this problem ? A huge thanks in advance to help me solve this issue.

Code

function createNewSheets() {
  // 1. Retrieve the current sheet names.
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNames = dataSheet.getSheets().map(s => s.getSheetName());

  // 2. Retrieve the values from "mastersheet" sheet.
  var masterSheet = dataSheet.getSheetByName('Liste de nageurs');
  var values = masterSheet.getRange('A2:B' + masterSheet.getLastRow()).getValues();

  // Imports table from link
  var swimmerTable = '=IMPORTHTML(B1;"table";4)'
  // Concatenates event with pool size
  var fullName = 'CONCATENATE(C2;" - ";D2)'

  // 3. Using the retrieved sheet names and values, the new sheets are inserted and the values are put.
  values.forEach(r => {
    if (!sheetNames.includes(r[0])) {
      var newSheet = dataSheet.insertSheet(r[0])
      sheetNames.push(r[0]);
      newSheet.appendRow(r);

      // Sets the formula in the 3rd column:
      newSheet.getRange(1, 3).setFormula(swimmerTable)

      // Sets the formula2 and iterates it from row 2 to row 50
      newSheet.getRange("J2:J50").setFormula(fullName)
    }
  });
}

Answer 1

I believe your current situation and your goal as follows.

• You have 2 Spreadsheets "A" and "B", and each Spreadsheet has the container-bound script.

• The script of Spreadsheet "A" is as follows.

    function exportDataNageurs() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var values = ss.getSheetByName('Nageurs').getRange('A2:B').getValues();
      var dataSheet = SpreadsheetApp.openById("###");
      var targetSheet = dataSheet.getSheetByName("Liste de nageurs");
      targetSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
    }
  

• The Spreadsheet "B" has the script of createNewSheets() in your question.

• When the script exportDataNageurs() of Spreadsheet "A" is run and the values are put to Spreadsheet "B", you want to automatically run the script createNewSheets() of Spreadsheet "B".

• You want to achieve this using the trigger.

Issue and workaround:

When I saw your script of exportDataNageurs(), the values are put using Spreadsheet service with SpreadsheetApp. In this case, unfortunately, even when the values are put to the Spreadsheet "B", the OnChange trigger is not fired. This is the current specification at Google side.

In order to fire the OnChange trigger when the values are put to the Spreadsheet "B" using the script, I would like to put the values using Sheets API instead of Spreadsheet service. By this, when the values are put to the Spreadsheet "B" using Sheets API, the OnChange trigger can be fired.

Usage:

1. Modified script of Spreadsheet "A".

Please modify exportDataNageurs() as follows. Before you use this script, please enable Sheets API at Advanced Google services. And, please set the Spreadsheet ID.

function exportDataNageurs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var values = ss.getSheetByName('Nageurs').getRange('A2:B').getValues();
  var spreadsheetId = "###"; // Please set the Spreadsheet ID.
  Sheets.Spreadsheets.Values.update({values: values}, spreadsheetId, "'Liste de nageurs'!A2", {valueInputOption: "USER_ENTERED"});
}

2. Install OnChange trigger to Spreadsheet "B".

Please install the OnChange trigger to the function createNewSheets() at Spreadsheet "B". Ref

3. Testing.

After the OnChange trigger was installed to createNewSheets() at Spreadsheet "B", please run the script exportDataNageurs() at Spreadsheet "A". By this, the values are put to the sheet "Liste de nageurs" of Spreadsheet "B" and run the script createNewSheets().

I thought that in your script createNewSheets(), the event object might not be required to be used.

Note:

• As another method, I think that using the Web Apps which was deployed to Spreadsheet "B", when exportDataNageurs() is run, your goal can be also achieved by requesting from the script exportDataNageurs() to the Web Apps. Also, the function can be also run using Google Apps Script API. But I thought that above method using Sheets API might be simpler and suitable for your situation. So I proposed it.

References:

• Method: spreadsheets.values.update
• Installable Triggers