Search code examples
google-sheetsgoogle-apps-script

Google Apps Script OnChange() trigger only fires when change is made in Sheet1 only

So, I have a Google Sheet in which I am importing some data from another sheet using Query + Importrange.

Formula in A2: =Query(IMPORTRANGE("https://docs.google.com/spreadsheets/XXXXXX","Sheet1!A2:G"),"select *",0)

It has an H column of dropdown type. The structure looks like this:

enter image description here

Now, what I need to do is when a new row is added then change the H column dropdown value to the next item in the list based on what was in the last row. So, if the value of H column in last row was Alex then in the next(newly added row) it should change to Amek.

I am using this script and it works btw but in Sheet1 only.

function myFunction(e) {
  const ssn = e.source.getActiveSheet().getName();
  const ss = SpreadsheetApp.getActive().getSheetByName(ssn);

  //ss.getRange("I2").setValue(ssn);

  var rg = ss.getRange(2, 8, ss.getLastRow() - 1);
  var vl = rg.getValues();
  var dv = rg.getDataValidation().getCriteriaValues()[0];

  // Find the last non-empty cell value in column 8
  var lastCellValue = null;
  for (var i = vl.length - 1; i >= 0; i--) {
    if (vl[i][0] !== '') {
      lastCellValue = vl[i][0];
      break;
    }
  }

  // If there's no lastCellValue found, start from -1
  var lastIndex = lastCellValue !== null ? dv.indexOf(lastCellValue) : -1;

  vl.forEach((r, i) => {
    var c = rg.getCell(i + 1, 1);
    if (c.isBlank()) {
      // Calculate the new index by adding 1 to the last index
      var newIndex = (lastIndex + 1) % dv.length; // Use modulo to wrap around if necessary
      // Set the new value in the current cell
      c.setValue(dv[newIndex]);
      // Update lastIndex for the next iteration
      lastIndex = newIndex;
    }
  });
}

The problem is I have two more tabs/sheet in the same Spreadsheet and with the same structure, just data being imported from another different source....I am triggering this script via onChange trigger because using onEdit is not possible but it only works when a new row is added in the Sheet1.

I want it to work for all sheets/tabs such as Sheet2, Sheet3, etc., in the spreadsheet in which the new row was added.

Thanks

Solution

  • In the event you just want to fill column H with names in a round-robin fashion, you do not need a script. Remove the dropdowns and use this formula in cell H2 instead:

    =let( 
  keys, A2:A, 
  values, { "Alex", "Amek", "Dipem", "Mehul", "Rajan" }, 
  numKeys, xmatch("?*", keys, 2, -1), 
  numValues, counta(values), 
  map(sequence(numKeys, 1, 0), lambda(i, 
    index(
      values, 
      mod(i, numValues) + 1 
    ) 
  )) 
)