Hey everyone! I’m trying to figure out how to grab the cells a user has highlighted in Google Sheets using Apps Script. I’ve got a function that I think might work, but I can’t test it until I publish my app. Here’s what I’ve come up with so far:
function getSelectedCells() {
let spreadsheet = SpreadsheetApp.getActive();
let activeSheet = spreadsheet.getActiveSheet();
let selectedRange = activeSheet.getActiveRange();
let selectedValues = selectedRange.getValues();
return selectedValues;
}
Does this look right to you? I’m not sure if it’ll actually grab the user’s selection when the script runs. Any tips or better ways to do this would be super helpful. Thanks!
Your approach is on the right track, but there are a few considerations. The getActiveRange() method indeed captures the current selection when the script executes, not at the moment the user makes the selection. For real-time tracking, consider implementing an onSelectionChange(e) trigger. This trigger activates whenever the user changes their selection, allowing you to capture it immediately. Additionally, adding error handling to manage cases where no cells are selected might be useful.
Below is a modified version:
function onSelectionChange(e) {
var range = e.range;
var selectedValues = range.getValues();
// Process the selectedValues as needed
Logger.log(selectedValues);
}
Remember to set up the trigger in your script editor for this code to work properly.
hey dancingfox, ur code looks pretty solid! one thing tho, it’ll only grab the active range when the script runs, not necessarily what the user selected. if u want real-time selection, you might wanna look into custom menus or onSelectionChange() trigger. that could give ya more control over when to capture the selection.
I’ve worked with Google Sheets API quite a bit, and your approach is generally correct. However, there’s a catch when dealing with user selections. The getActiveRange() method captures the selection at the moment the script runs, not when the user actually selects cells.
For more dynamic interactions, I’d suggest using custom sidebar or dialog interfaces. These allow you to create buttons or other UI elements that users can click after making their selection. When they click, you can then run your getSelectedCells() function to capture the current selection.
Another approach is to use the onSelectionChange() trigger, as others have mentioned. This can be powerful but be cautious - it fires very frequently and can slow down your sheet if not implemented carefully.
Remember to always test your scripts thoroughly before deploying. Sometimes what works in theory can behave differently in practice, especially when dealing with user interactions.