Friday, June 28, 2013

Google Spreadsheet Navigate to another Sheet

Question:

Hello. I am trying to link cells in in one Google spreadsheet to cells in another, related sheet. Which is to say, I would like to not only pull data from sheet 1 to populate sheet 2, ideally, I would be able to click on the data in sheet 1 and it would take me to that cell in sheet two. Is this possible?


Solution:

As of now (June 2013), you can't directly set any cell to navigate you to another sheet. But I will show you the work around of doing so.

First of all insert an image, which can be of any size, for this example I have made an image of small size which looks like a button. To insert image, go to "Insert" Menu then select "Image..."



Have a look at the screenshot below:


Now, to move this button anywhere, just right click on it and drag it to your desired position. And also now you have to assign script to it, for that again right click on it and the select "Assign script...".



After that you have to give the function name. You have to write same function name in your script editor.


I have named it "myFunction". And now you have to go to script editor and make a new script. Delete all the existing code in the script file and insert the following code:

function myFunction()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet2");
  ss.setActiveSheet(sheet).setActiveSelection("D5");

}

In above code, you can see I have used to navigate to Cell D5 of "Sheet2", which you can change as per your requirement. But keep the function name same as that you have inserted in the "Image" >> "Assign script.."

That's it, now you are almost done. For the first time run the script function "myFunction" from script editor. And then you can click on the image file, and by clicking on it you can navigate to "Sheet2" Cell D5.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

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.


I also take up private and confidential projects:
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,

12 comments:

  1. Hi Kishan:

    I"m having trouble with Script Editor. I posted this in my script:

    function ILIKEYOU()
    {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("ILIKEYOU");
    ss.setActiveSheet(sheet).setActiveSelection("A18");

    }

    I then test it and I get a message that says: Invalid argument (line 5, file "Code")

    What seems to be the trouble??

    ReplyDelete
    Replies
    1. Hi Neil,

      For me, your code is working fine... I don't get any error message..
      Can you share that spreadsheet?
      You can also try the following code:

      function ILIKEYOU() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("ILIKEYOU");
      var range = sheet.getRange("A18");
      ss.setActiveSheet(sheet).setActiveRange(range);
      };

      Delete
  2. Thank you, Kishan!

    I will try your new code.

    I also sent you my spreadsheet to look at.

    Thanks for your help!

    -Neil

    ReplyDelete
    Replies
    1. Hi Neil,

      I have modified the code in your spreadsheet's script editor. Have a look a the following code:

      function ILIKEYOU()
      {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("I LIKE YOU");
      sheet.setActiveRange(sheet.getRange("A18"));
      };

      Hope this helps.

      Delete
    2. I'm trying to go to a certain cell within my google sheets.. I've tried both and can't get to work
      the 1st method gets me an invalid argument on line 5
      the 2nd says something about a nul value....so frustrated

      Delete
  3. =IMAGE("google.png"; FUNCTION(myFunction)) ????

    Thanks....

    ReplyDelete
  4. Hi,

    I've used this and it works greats. I've since shared the sheets to a colleague to test out but unfortunately when they click on the image that should take them somewhere else it's not allowing them and saying I need to give them access (but have given them access to sheets). Could you let me know what I need to amend so they have full functionality?

    ReplyDelete
  5. Hi,

    Thank you for this post, it is very helpful!

    Is there a way to edit this script so once it navigates to a cell, it automatically changes the view so the cell is at the top of the sheet?

    Thanks!
    Vicki

    ReplyDelete
  6. this stack overflow question is based on your tutorial.. can you please take a look?
    http://stackoverflow.com/questions/37607771/how-to-make-a-google-sheets-function-reference-the-cell-it-was-called-from

    ReplyDelete