0

i am trying the below code in order to automatically scroll to the end of the sheet when opening it . However i would like it to work whenever i select another sheet , not having to refresh it in order to work

  function onOpen(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
   
  if (sheet.getName().startsWith('Test')){

    var lastrow = sheet.getLastRow();
    var range = sheet.getRange(lastrow,1);
    sheet.setActiveRange(range) ;
  }

Any help will be appreciated

George Papadopoulos
  • 65
  • 7
  • 1
    I have to apologize for my poor English skill. Unfortunately, I cannot understand `work whenever i select another sheet`. Can I ask you about the detail of it? – Tanaike Mar 09 '23 at 00:44
  • Yes maybe i didn't express it correct. My spreadsheet consists of 4 tabs , i would like for the script to be applied to every tab whenever i select it. This now works if i select the tab and refresh it (i guess because it's an onOpen() function). I would like it to work without refreshing it, just by selecting the tab. – George Papadopoulos Mar 09 '23 at 07:49
  • 1
    Thank you for replying. From your reply, I proposed a sample script as an answer. Please confirm it. If I misunderstood your question and that was not useful, I apologize. – Tanaike Mar 09 '23 at 08:37

1 Answers1

0

From your following reply,

My spreadsheet consists of 4 tabs , i would like for the script to be applied to every tab whenever i select it. This now works if i select the tab and refresh it (i guess because it's an onOpen() function). I would like it to work without refreshing it, just by selecting the tab.

I believe your goal is as follows.

  • When you active a tab on Spreadsheet, you want to activate the cell of the last row of column "A".

In this case, I remembered this thread. I thought that this method might be able to be used for your situation. When this is reflected in your script, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. And, in order to initialize the active sheet, please reopen Google Spreadsheet. By this, onOpen is run and the initial sheet name is set to Properties Service.

function onOpen(e) {
  const prop = PropertiesService.getScriptProperties();
  const sheet = e.range.getSheet();
  const sheetName = sheet.getSheetName();
  prop.setProperty("previousSheet", sheetName);
  yourScript(sheet);
}

function onSelectionChange(e) {
  const prop = PropertiesService.getScriptProperties();
  const previousSheet = prop.getProperty("previousSheet");
  const sheet = e.range.getSheet();
  const sheetName = sheet.getSheetName();
  if (sheetName != previousSheet) {
    yourScript(sheet);
  }
  prop.setProperty("previousSheet", sheetName);
}

// --- This is your script.
function yourScript(sheet) {
  var lastrow = sheet.getLastRow();
  var range = sheet.getRange(lastrow, 1);
  sheet.setActiveRange(range);
}
  • When you select another tab, onSelectionChange is automatically run. And, it checks whether the tab is changed. When the tab is changed, the function yourScript is run. By this, the last row of column "A" is activated.

Testing:

This script is used, and the following situation is obtained.

enter image description here

In this sample, the active cell is on cell "A1" in the 1st sheet. But, in the actual script, when Spreadsheet is opened, the last row of column "A" is activated.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Hi @Tanaike thank you for your reply , this answers excactly my questions but unfortunately i tried it and it doesnt work for me . Nothing happens when i switch tabs. Does the fact that i also have an on edit function in my script has anything to do with it ? – George Papadopoulos Mar 09 '23 at 15:21
  • 1
    @George Papadopoulos Thank you for replying. I apologize for the inconvenience. As I have already shown, when my script is used, the above situation is obtained. You can see the sample demonstration in my answer. But I would like to support you. From your reply, in this case, it is required to know your current situation. So, can you provide your spreadsheet including my proposed script for correctly replicating `Nothing happens when i switch tabs.`? If you can cooperate to resolve your issue, I'm glad. Can you cooperate to do it? – Tanaike Mar 10 '23 at 02:12
  • Thank you for your time. I ve tried the script in a brand new spreadsheet and it works.Though when i run it on my desired spreadsheet where i aslo have several other functions under an onEdit() functions it only works in the first sheet – George Papadopoulos Mar 10 '23 at 08:08
  • @George Papadopoulos Thank you for replying. From `I ve tried the script in a brand new spreadsheet and it works.`, I understood your issue was resolved. About your new issue of `Though when i run it on my desired spreadsheet where i aslo have several other functions under an onEdit() functions it only works in the first sheet`, unfortunately, I cannot understand it. – Tanaike Mar 10 '23 at 11:51
  • @George Papadopoulos But, I would like to support you. So, can you provide the detail of your new issue? In this case, it is required to know your actual situation. So, can you provide the sample Spreadsheet for replicating your new issue? By this, I would like to confirm it. When you can cooperate to resolve your new issue, I'm glad. Can you cooperate to do it? – Tanaike Mar 10 '23 at 11:51
  • @George Papadopoulos I think that the reason that I cannot imagine your current script from `Though when i run it on my desired spreadsheet where i aslo have several other functions under an onEdit() functions it only works in the first sheet` is due to my poor English skill. I apologize for this. – Tanaike Mar 10 '23 at 11:59