1
function onInsertColumn(activeRng) {  
  /* I coded this function, and it works, so I'll omit 
  the implementation here because it's irrelevant. 
  But I haven't figured out how to get the active range
  from the onChange event (to pass to this function).*/
}

function onChange(event) {
  // https://stackoverflow.com/a/66686524/470749
  // https://stackoverflow.com/a/64454240/470749
  // https://developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers
  // https://developers.google.com/apps-script/guides/triggers/events
   
  if(event.changeType == 'INSERT_COLUMN'){
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getActiveSheet()
    const activeRng = sheet.getSelection().getActiveRange(); // WHAT SHOULD GO HERE?
    onInsertColumn(activeRng);
  }
}
Ryan
  • 22,332
  • 31
  • 176
  • 357
  • I think that in your situation, there are 2 patterns. One is that the column is manually inserted. Another is that the column is inserted by Sheets API. When the column is inserted by Sheets API, I think that the column number can be known from the request body. When the column is manually inserted, when your script is used, how about `sheet.getSelection().getActiveRange().getColumn()`? Or, when the event object is used, how about `event.source.getActiveRange().getColumn()`? But, if I misunderstood your question, I apologize. – Tanaike May 23 '22 at 02:40
  • Yeah that's a problem with the onChange event. It doesn't really give you much information. – Cooper May 23 '22 at 02:58
  • you can check all available informations by adding `Browser.msgBox(JSON.stringify(e))` – Mike Steelson May 23 '22 at 06:24
  • So are you trying to find out which column has been inserted programmatically rather than manually? – ziganotschka May 23 '22 at 07:30
  • The active range is the column that was inserted. I'd yank out the `getSelection()` part though, so that it'll work with any type of insertion. You're not presenting a problem in the question. Does your code not work or what range is currently sent to `onInsertColumn`. What are you expecting? What happens instead? – TheMaster May 23 '22 at 08:08

1 Answers1

1

Explanation:

As users have mentioned already in the comments, it depends on how the column is inserted. If that is done manually via the spreadsheet UI then you can get the number of the active column that is selected:

const colNumber = sheet.getSelection().getActiveRange().getColumn();

and then create a range object that will represent the full column range:

const activeRng = sheet.getRange(1,colNumber,sheet.getMaxRows(),1);

then you can feed this range to any function.

Of course instead of getMaxRows() you can also use getLastRow() to get the up until the last row with content instead of the full column range.

Solution when manually inserting a column:

function onInsertColumn(activeRng) {  
  // example
  activeRng.setValue("Selected");
}


function onChange(event) {
  // https://stackoverflow.com/a/66686524/470749
  // https://stackoverflow.com/a/64454240/470749
  // https://developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers
  // https://developers.google.com/apps-script/guides/triggers/events
   
  if(event.changeType == 'INSERT_COLUMN'){
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getActiveSheet();
    const colNumber = sheet.getSelection().getActiveRange().getColumn();
    const activeRng = sheet.getRange(1,colNumber,sheet.getMaxRows(),1);
    onInsertColumn(activeRng);
  }
}
Marios
  • 26,333
  • 8
  • 32
  • 52
  • 1
    Thank you so much! Part of my problem (which I later noticed in error email notifications that I'd received) was that I'd moved a line, and it was (other than the emails) silently failing because of my `sheet` variable no longer being defined. – Ryan May 23 '22 at 11:22