CODEWITHSUNDEEP
google-apps-script
Dmitry
Dmitry

Reputation: 23

Daily change of the active sheet in the Google Sheets

Can you please tell me how to automatically change the active sheet in google tables every day with Apps Script, like this: 1-2-3-1-2-3... And hide the inactive ones. This is needed to show the current dining room menu.

Upvotes: 0

Views: 149

Answers (2)

CMB
CMB

Reputation: 5163

Solution:

Apart from needing an Installable Trigger to have the script executed daily, this function should rotate the sheets per execution.

Prerequisites:

  1. Take the Sheet ID of your spreadsheet from the URL and replace SHEET-ID in the code: https://docs.google.com/spreadsheets/d/**SHEET-ID**/edit#gid=0
  2. Hide all sheets except one. The active sheet will be the starting point.

// execute this function first
function createTimeDrivenTriggers() {
  // Trigger every 24 hours. Set preferred time here:
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .atHour(10)
      .everyHours(24)
      .create();
}

// this should not be executed manually
function myFunction() {
  var ss = SpreadsheetApp.openById("SHEET-ID");
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (!sheets[i].isSheetHidden()) {
      sheets[(i+1) % sheets.length].showSheet();
      sheets[i].hideSheet();
      break;
    }
  }
}

Upvotes: 0

Marios
Marios

Reputation: 27348

Explanation:

You have three sheets in a spreadsheet file and your goal is to have only one sheet shown at a given day.

  • The first day you show only the first sheet and you hide the other two. The next day you hide all the sheets except for the second one and the third day you hide all sheets except for the third one.

  • The fourth day you start over again and you show only the first sheet.

This exercise needs two frameworks to work properly:

Solution:

The only thing you should do is to execute only and once the function createTrigger. The other function in this script is going to be executed every day automatically between 8-9 am and you should not execute it manually.

// execute only and once createTrigger
// Runs myFunction between 8am-9am in the timezone of the script
function createTrigger(){
  ScriptApp.newTrigger("myFunction")
    .timeBased()
    .atHour(8)
    .everyDays(1) // runs every day
    .create();
}

// this should not be executed manually
function myFunction() {
  const spreadsheet = SpreadsheetApp.getActive();
  const sheets = spreadsheet.getSheets();
  const sp = PropertiesService.getScriptProperties();
  const breakPoint = 2; // after third sheet start over
  const vs = sp.getProperty("visibleSheet");
  let index;

  if(vs){
    index = parseInt(vs);
  }
  else {
   index = 0;
   sp.setProperty("visibleSheet",index);
  }
  sheets.map(sh=>sh.showSheet());
  sheets.forEach((sh,i)=>{
      if(i!=index){
         sheets[i].hideSheet();
      };
  });
  index==breakPoint ? sp.setProperty("visibleSheet",0) 
                   : sp.setProperty("visibleSheet",index+1);

}

Things you can modify in the script:

  • Change 8 in atHour(8) if you want to execute the script in a different time rather than 8am.

  • Change variable breakPoint if you have more sheets (and not only 3). The counter starts from 0 this is why 2 indicates the third sheet. If you have six sheets then change it to breakPoint = 5;. In this way, the code is general and not restricted to the number of sheets.

Upvotes: 1

Related Questions