Monday, August 27, 2012

How to get the location of last modified Cell

How to get the location of last modified Cell


Following script will get you the location of last modified Cell:


function setTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("myFunction").forSpreadsheet(ss).onEdit().create();
}

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var sName = sheet.getName();
  var currentCell = sheet.getActiveCell().getA1Notation();
  UserProperties.setProperty("mySheetName", sName);
  UserProperties.setProperty("myCell", currentCell);
}

function onOpen() {
  var lastModifiedSheet = UserProperties.getProperty("mySheetName");
  var lastModifiedCell  = UserProperties.getProperty("myCell");
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(lastModifiedSheet).getRange(lastModifiedCell).activate();
}


When you run it for the first time, it will ask you for the authorization of this script as it is modifying User properties. 

You have to wait for few seconds when you open your sheet, to get the position of your lastly modified cell (as it takes time for a script to execute).




If you don't know how to write a script, or where to write this above script then look at the following link which will help you out:
http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 



I hope this script will help you to get the location of last modified Cell.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

41 comments:

  1. Well done, and very helpful, thank you. Yes, this does indeed work in our problematic spreadsheet.

    For the sake of other users, in order to install this script, do the following:
    - go to Tools, I had to Tools -> Script Editor
    - paste in the script
    - from the 'Select function' drop-down, choose 'setTrigger'
    - click the play icon
    - answer 'yes' to the security question
    - back in the script editor, select 'setTrigger' again and click the play icon again.

    ReplyDelete
    Replies
    1. When I select Tools, a window opens inquiring what I'm creating the script for:

      Blank Project
      Google Sheets Add-on
      Google Docs Add-on
      Google Forms Add-on
      Custom Functions in Sheets
      Web App

      I assumed "Custom Functions in Sheets" was correct, but when I selected that, it opened a window that already contained script. Do I insert the script above somewhere in that window? Or enter it into a new blank window that I create somehow?

      Completely new to scripting, so whatever additional guidance you can provide would be helpful.

      Thanks.

      Delete
    2. Clicking on tools and script editor causes new window to hang indefinitely.

      Delete
  2. Do I have to run this each time I open the spreadsheet? I am trying to follow the instructions and I get it to be authorized.. but I can't seem to get it to work..

    ReplyDelete
  3. Hi crwcpa,

    No you don't have to run it each time, after you have authorized this script then you just edit any Cell on any sheet (cell position will be remembered), you just closed the spreadsheet and open it again just wait for few seconds and allow the script to execute and you will notice that cursor will get back to it's last position where you edited the cell before you closed it.

    I hope it is clear now.

    Thanks,
    Kishan.

    ReplyDelete
    Replies
    1. Hi Kishan,

      Thank you for this post. One small query - Instead of closing and opening will Refresh(F5)'ing work?

      Delete
  4. Thanks for posting this.

    Very nice and very useful script.

    I had to execute the setTrigger function before it began working.

    Like what Berwyn says above.

    Awesome! Thanks again!

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Hi,

    thank you very much for this script, it works for me but I have one additional question.

    I have a cell at the top of my sheet that has a =today() , and this is calculated everytime I open the sheet, which nullifies the script's purpose. Is there a way I can script around this issue?

    ReplyDelete
  7. This works very well! Many thanks for your sharing this.

    ReplyDelete
  8. Hi,

    Does this script still work? I've followed all the steps, but nothing seems to be triggering.

    ReplyDelete
  9. script does not work for me... spent quite some time debugging with no success

    ReplyDelete
  10. No luck either! Following steps to the letter and... absolutely nothing. Not impressed here

    ReplyDelete
  11. I was able to play around with this a bit and get it to work for me! Doesn't seem to work on the Android Tablet but that's okay! Thank you, love it!!!

    ReplyDelete
  12. Worked well for me. Thanks for sharing. One point to note - you need to edit a cell to be taken back to that cell when you open the spreadsheet the next time.

    If you merely position your cursor on a cell, it will not take you back when you reopen the spreadsheet.

    ReplyDelete
    Replies
    1. Yep - this did the trick for me as well. Thanks.

      Delete
    2. Thank you! That did it for me as well, whew!

      Delete
  13. I could not get this to work at first. I tried messing with Triggers and whatever I could find, but no luck. Finally I realized I had filters turned on, so I tried turning them off, closed the sheet, re-opened it, and then it worked great! Hope that helps anyone having issues with it. Thanks for the code!

    ReplyDelete
    Replies
    1. Thanks for the tip! This made it work for me as well.

      Delete
  14. I was able to make it work. You need to configure 2 triggers:

    - One "onOpen" trigger that runs the "onOpen" function of this script
    - One "onEdit" trigger that runs the "myFunction" function of this script.

    See screenshot: http://tinypic.com?ref=zw0201

    ReplyDelete
  15. It will not work for me. Script runs fine, no errors, but when I try to actually close the sheet and reopen to last save point, nothing happens. It just opens as default

    ReplyDelete
  16. Thank you - finally a method for returning to your last saved point. Thanks for the script Kishan and thanks to Berwyn for the installation guide.

    ReplyDelete
  17. Awesome, this works for me (original instructions at the top of the page). I've now got this running on a couple of different Sheets no bother :) Thanks

    ReplyDelete
  18. Thank you, it works for me! but How can i improve it and make it send me to the last modified cell of in every sheet and not of all?

    ReplyDelete
  19. Ok, so I know nothing about script editing and am 69 and really do not care to take the time to learn now... BUT if there is ONE person out there who Writes the Script, and then lets us Copy and Paste it in the Cell we want to open, well, that would be great... I do not understand with all the tech out there that Google has NOT given us a button to push "Start here when opening again". For example when getting ready to close, give us optional button to push, and when we push it, it will automatically ask us what cell do you want to start in when you reopen it?" I mean, that should be freaking easy.

    ReplyDelete
    Replies
    1. You can do a similar thing yourself. Go to the cell (the one you want to arrive at upon a re-open of the shee), right click and choose 'Get link to this cell'. It will show you a typical spreadsheet URL but also, tagged to the end, will the the cell info. So copy all of this URL.

      You have to add this copied URL to your browser's list of bookmarks; then you can click this link at any time and arrive at said cell.

      Delete
  20. I get TypeError: Cannot call method "getRange" of null. (line 17, file "Code")

    ReplyDelete
  21. The only change I had to make to the original instructions was that after opening Tools -> Script Editor there was a small amount of script already in place:

    function myFunction() {

    }

    I had to delete this before pasting the script - after that it was as Berwyn says in the OP.
    Thank you - fantastic piece of script that will save me tedious hours of finding my last working point.

    ReplyDelete
  22. This comment has been removed by the author.

    ReplyDelete
  23. I have some very elaborate sheets with multiple tabs. This works to get it to open to the cell I was last working in, but each tab I go to goes to the top of the page. Is there a way to get each tab to recall the last cell I was in?

    ReplyDelete
  24. I had to activate the Set Trigger function as well. I had a little trouble with line 17 of the my Open function, but I ran the debug, saved it, closed the sheet and then re-opened it. It took about 10-12 seconds after opening but went right to the last cell I modified on line 5487. Sure beats Page Down. Alleluia!!!

    ReplyDelete
  25. This should be a basic functionality for all documents, not only Google Sheets but also Good Docs and the rest, and thus it should not be something that the user has to do through "script" thingy, but it should be the default automatic behavior for all documents of all types.

    When I read a book, continuing reading on the same page I was at is the default behavior.

    Similarly, when I work on a document, continuing working on the same position I was at should also be the default behavior.

    ReplyDelete
  26. Useful article, thank you for sharing the article!!!

    Website bloggiaidap247.com và website blogcothebanchuabiet.com giúp bạn giải đáp mọi thắc mắc.

    ReplyDelete