changing the active cell onEdit

[Saul Cozens]

Hi,

I'm trying to insert a row in a sheet based on an onEdit trigger.

After the row is inserted, I need to reset the active cell of the user's UI to shift it down  a row if the row was inserted above the current active cell.

Problem is the calling getActiveCell() within the onEdit triggered function returns the cell that was edited and not where the user navigated to next.

The only way I can think to update the active cell is to set a timeout function to change it a few milliseconds later. This doesn't feel like a sensible/robust approach. Is there a better way?

TIA

saul

[Steve Webster]

You could try "offset".

https://developers.google.com/apps-script/reference/spreadsheet/range#offset(Integer,Integer)

var activeCell = e.range;

// Your Insert Row Code

activeCell.offset(2, 0).activate(); // not sure if offset should be 1 or 2 rows

Kind Regards,

Steve Webster

SW gApps, President 

Google Product Expert in: Google Apps Script, Drive, and Docs 

Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 

Add-ons: Text gBlaster and Remove Blank Rows

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/45f27115-2220-432e-b1b7-4041bde33ffb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Saul Cozens]

that's a useful function that I didn't know about, but the problem is that e.range reports the change of the cells that were edited not the cells that the user clicked on (or tabbed to) after editing. So the active cell becomes the one below the edited cell, even if they tabbed to the right or clicked elsewhere.

saul

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BV5gQ4sVjyJxHQsZbPeY4Bj0NG_YJX602MC-PA_NRAh2pywGA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

--------------------------------------------------------------
mob: 07971 447167
twitter: @czndigital
web: czndigital.com
company reg: 08399149
vat: 156 5813 91

[Steve Webster]

In that case, it sounds like you need to get the active cell with "sheet.getActiveCell()" (where sheet is a variable).

So with onEdit you can identify the range, and it's row; with your insert you know it's location, and now with .getActiveCell() know the current range and it's row. I hope this helps.

Kind Regards,

Steve Webster

SW gApps, President 

Google Product Expert in: Google Apps Script, Drive, and Docs 

Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 

Add-ons: Text gBlaster and Remove Blank Rows

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABZCicEsZM-2cbqnCF1Gd2MgLsfd%3DknSk0%2B-%2BPjyg6jAcGDh-g%40mail.gmail.com.

[Saul Cozens]

thanks for your thoughts so far, but I'm afraid getActiveCell() also returns the edited cell rather than the one activated after.

var activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();

// Your Insert Row Code

activeCell.offset(1, 0).activate(); 

Does exactly the same

saul

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BV5gQ6ppSzpKwfobxT1b99e0voHhRADd0Wu8oxBbOgt6Qif5w%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Steve Webster]

You need to get the active sheet again before the .getActiveCell();

Kind Regards,

Steve Webster

SW gApps, President 

Google Product Expert in: Google Apps Script, Drive, and Docs 

Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 

Add-ons: Text gBlaster and Remove Blank Rows

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABZCicHjthEq%2BWRUsXEx6rOG_rQKc9zAbDr8%3Df1eSQMg3QUNpg%40mail.gmail.com.

[Saul Cozens]

Sorry Steve, I don't understand?

I'm already calling getActiveSheet before getActiveCell:

var activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();

// Your Insert Row Code

activeCell.offset(1, 0).activate(); 

saul

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BV5gQ4peYbLanF1f%2B1rBw%2Bue6tYfHarNmr2w%3DJ3hx-bZDKzUw%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Steve Webster]

That's okay. A couple of thoughts.

1. replace onEdit with myCustomOnEdit() where you need to setup a trigger event (on-edit). In other words, copy your code to this new function and comment-out onEdit().

2. The myCustomOnEdit(event) { var activeCell = e.range; } gets your active cell. Then do your if condition and insert row stuff. Then after that code var activeCellAfterStuff = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();

Kind Regards,

Steve Webster

SW gApps, President 

Google Product Expert in: Google Apps Script, Drive, and Docs 

Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 

Add-ons: Text gBlaster and Remove Blank Rows

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABZCicH_SxWwY1ERv%2B9876oSVeravrP5NRxfRyUDjrWd24uosA%40mail.gmail.com.

[Alan Wells]

If this is a process, and you need to save a value that persists from one action to another, then you can save a value to PropertiesService.

[Saul Cozens]

Thanks Alan,

The Properties Service is likely to be useful, but I don't think it will solve this problem.

I suspect that the problem is that the onEdit trigger fires with a copy of the current status of the sheet (and the activeRange) BEFORE the user focus is moved to the new cell/range. So unless there is an afterEdit trigger that I don't know about, I think it is impossible to know where the user focuses after doing an edit.

cheers

Saul 

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b91f100a-5f74-45f6-947c-a39491f4c62e%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Steve Webster]

I have written a solution for a client with a lot of myOnEdit triggers.

1. If on-edit event is a cell

2. If that active cell meets more criteria

3. Do stuff like copy content from row above to current active row

4. If user quickly clicks on another cell, it did not matter because the client wanted to move the active cell to a specific cell and we did so. This was supplemented with user training.

Remember the on-edit fires when a cell's old value changes to a new value. Therefore, if someone quickly selected a dropdown value in A2 and then A3, then two on-edit events have been invoked -- one after the other.

Kind Regards,

Steve Webster

SW gApps LLC, President 

Google Product Expert in: Google Apps Script, Drive, and Docs 

Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 

Add-ons: Text gBlaster and Remove Blank Rows

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABZCicFgwbk-pDc%3DLg2zDEd2zG43SJEcU3owKGhDNdor_GJVtg%40mail.gmail.com.

[Alan Wells]

Yah, there is no "on cell click" event, or any type of detection for a user making a Sheet cell active.  That's a common request made.

On Tue, Jun 11, 2019 at 2:56 AM Saul Cozens <sa...@czndigital.com> wrote:

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABZCicFgwbk-pDc%3DLg2zDEd2zG43SJEcU3owKGhDNdor_GJVtg%40mail.gmail.com.