-1

Mind has gone blank! I have a Google Spreadsheet comprising three sheets. I want to manually select a row in the second (named) sheet for deletion. I select the row, but when I try to access the selection all I get every time is row1, col1 on the first sheet. The code I am using is as follows:

function cancelBooking() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var bookDB = ss.getSheetByName('Booking Database');
  bookDB.activate; //not sure this is really necessary
  var selection = bookDB.getSelection();
  var activeRange = selection.getActiveRange();
  activeRange.setFontColor('green');
  etc

But all I get is the top left cell in the first sheet turning green. What am I missing?

PhilTilson
  • 55
  • 5
  • Only the cells which are selected are highlighted, are you sure that you are selected a larger range, for example: A2:F10? – Neven Subotic Oct 22 '21 at 17:50
  • No - on the Bookings sheet, I select an entire row by clicking on the row number; this highlights the row. But the code leaves that row completely unchanged and just changes the colour of the top left cell on a different sheet! – PhilTilson Oct 22 '21 at 17:53
  • `function getCurrentRow() { const ss = SpreadsheetApp.getActive(); const cell = ss.getActiveCell(); Logger.log(cell.getRow()) }` – Cooper Oct 22 '21 at 17:58
  • The line "const cell = ss.getActiveCell();" just returns "Exception: Please select an active sheet first". – PhilTilson Oct 22 '21 at 18:08
  • It works in my spreadsheet – Cooper Oct 22 '21 at 18:09
  • Please see answer below, since you are calling a `getSheetByName()` you basically tell the app to select that sheet's A1 cell. From then you can are doing `getSelection()`, which is A1. – Neven Subotic Oct 22 '21 at 18:23
  • That would be fine - except, as I said, the A1 cell that is selected is on a DIFFERENT sheet - not the bookDB sheet! That's what I can't understand. But even assuming it selected the correct sheet, how can I then access the highlighted row? – PhilTilson Oct 22 '21 at 18:25

1 Answers1

1

So a few things:

bookDB.activate is a property, which does not exists. If you want to active it, which is not necessary, then you would call the function bookDB.activate(). What I think you want to is is ss.getActiveSheet(), which is the currently open and active sheet, and then you should pick up the currently active range.

What you are doing now is telling to the app to go to this sheet by name, which returns the sheet and the first cell. You then activate this cell and select it, but the app is still only on the first cell.

With what I said above, you can do this

 function setFontColorToRed(){
    const range = SpreadsheetApp.getActiveSpreadsheet().getActiveRange()
    range.setFontColor("red");
 }

Or you can even skip a few things an go ss.getActiveRange()

References

Neven Subotic
  • 1,399
  • 1
  • 6
  • 18
  • Nope! Still turns A1 of the first sheet red and leaves the selected row in bookDB untouched! – PhilTilson Oct 22 '21 at 18:33
  • I tested it and wit worked. Are you sure that you want the `FONT COLOR` to be a certain color or do you want the `FONT BACKGROUND` to be a certain color? If you can, copy your sheet and create a public file which contains your error. I am not able to reproduce it. – Neven Subotic Oct 22 '21 at 18:39
  • Will come back on this in a couple of hours - have to go out! But the color or background are irrelevant - it's doing it to completely the wrong cell(s)! – PhilTilson Oct 22 '21 at 18:41
  • I've simplified it even more, just make sure that you have the cells currently selected. This does not work if you have another sheet active, as the selection only refers to the one selection in the currently active sheet, example: If you select range A1:B9 in Sheet A, then you click on Sheet B and select A1, then you cannot call `getSelection()`on Sheet A, as it can only refer to your currently active sheet B. – Neven Subotic Oct 24 '21 at 10:17