Google Spreadsheet Script - Dynamically change cell content based off currently selected cell?

2
votes

I recently started messing around with google script for spreadsheets and I was wondering if there is a way to change a cells value based on the currently selected cell? For example, if the cell displaying the row number is in A1 and the user highlights B8, A1 would display '8'. Is that even possible?

Flipping through the google apps-script spreadsheet services page and I noticed a class called .getActiveCell and I'm assuming that would be a key part but I'm kind of lost. Can anyone point me in the right direction?

Thank you for any help.

2
google-apps-scriptgoogle-sheets

2 Answers

1
votes

This small code snipet does partially what you need but is unable to detect a simple cell selection, it fires only when the content of a cell is modified.

function onEdit(){
  var ss = SpreadsheetApp.getActive();
  var cell = ss.getActiveCell();
  ss.getRange('A1').setValue(cell.getA1Notation().replace(/[^0-9]/g, ''));// get A1 notation and rip off any non numeric value to keep only the row number
}

As far as I know there is no direct method to get the selection on a simple click or keypress.

0
votes

Google Spreadsheet Script allows registering an onChange(e) trigger:

Installable Triggers

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('myFunction')
      .forSpreadsheet(ss)
      .onChange()
      .create();
}

I haven't tried if this shoots when a selection is changed.