I am creating two lists of links on my opening sheet (9060DASH) in Google Sheets. It looks like this:
One list is links to all sheets that are not hidden (Active) and one to all that are hidden. Each item must meet two other criteria to be included:
- The sheet name must not include the numbers "9060."
- The cell GH3 in the sheet must contain "Protected."
I have a functioning script, but it is loading too slowly. Here is a sample to show how it loads during onOpen(). How can I do this more efficiently? Can it be done better using "push"? Here is the script:
function populateSheetList() {
SpreadsheetApp.getActive().getSheetByName("9060DASH").activate();
var ss = SpreadsheetApp.getActive();
var ui = SpreadsheetApp.getUi();
ss.getRange('9060DASH!D4:E100').clear();
var counter = 4;
var sheetName = "";
var cellID= "";
var richValue = "";
var sheetURL = ss.getUrl();
var sheetLink = ""
var mySheet = "";
var shouldNotContain = '9060'; //array code from stackover to build array without 9060 sheets
var sheetNames = SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => s.getName());
var filtered = sheetNames.filter(x => !x.toLowerCase().match(shouldNotContain.toLowerCase()));
/** CREATES ACTIVE SHEETS LIST */
for(var i =0;i<filtered.length;i ){
cellID = '9060DASH!D' counter;
sheetName = filtered[i];
mySheet = ss.getSheetByName(sheetName);
sheetLink = sheetURL '#gid=' mySheet.getSheetId();
if(mySheet.isSheetHidden() == false){
if(ss.getRange(sheetName "!GH3").getValue() == "Protected"){
richValue = SpreadsheetApp.newRichTextValue()
.setText(sheetName)
.setLinkUrl(sheetLink)
.build();
ss.getRange(cellID).setRichTextValue(richValue);
counter = counter 1;
}
}
}
/** GATHERING HIDDEN SHEETS */
var counter = 4;
for(var i =0;i<filtered.length;i ){
cellID = '9060DASH!E' counter;
sheetName = filtered[i];
mySheet = ss.getSheetByName(sheetName);
sheetLink = sheetURL '#gid=' mySheet.getSheetId();
if(mySheet.isSheetHidden() == true){
if(ss.getRange(sheetName "!GH3").getValue() == "Protected"){
richValue = SpreadsheetApp.newRichTextValue()
.setText(sheetName)
.setLinkUrl(sheetLink)
.build();
ss.getRange(cellID).setRichTextValue(richValue);
counter = counter 1;
}
}
}
}
CodePudding user response：
I have reduced the time to load the dashboard by rearranging a number of things and by getting a final array before writing anything out to the dash. I reformatted the cells and sorted the records before the evaluation loops began. I added more status messages, to keep users engaged while the background work was going on. I put the criteria of separating active from hidden sheets last and, depending on which, wrote the values to two different arrays (actvSheets and hidnSheets). Then I processed each array out to the dashboard. I did this in for loops. I suspect I could save even more time to write them out as one command from each array, but I have not been able to figure out how to write out in vertical form. Here is the code. Any refinements are welcome!
/**
* =================
* populateSheetList
* =================
* This function builds the available sheets in the
* 9060DASH sheet--containing all with the Protected
* status.
*/
function populateSheetListNEW() {
msgDash("Clearing sheet list . . . ")
// SpreadsheetApp.getActive().getSheetByName("9060DASH").activate();
var ss = SpreadsheetApp.getActive();
var ui = SpreadsheetApp.getUi();
ss.getRange('9060DASH!D4:E100')
.clear()
.setFontSize(14)
.setHorizontalAlignment('left')
.sort({column: 4, ascending: true})
msgDash("Evaluating sheets . . . ")
var counter = 4;
var sheetName = "";
var cellID= "";
var richValue = "";
var sheetURL = ss.getUrl();
var sheetLink = ""
var mySheet = "";
var actvSheets = [];
var hidnSheets = [];
msgDash("Eliminating administrative sheets . . . ")
var shouldNotContain = '9060'; //array code from stackover to build array without 9060 sheets
var sheetNames = SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => s.getName());
var filtered = sheetNames.filter(x => !x.toLowerCase().match(shouldNotContain.toLowerCase()));
var sortFilt = filtered.sort();
/** CREATES SHEET ARRAYS */
msgDash("Gathering census worksheets . . . ")
for(var i =0;i<sortFilt.length;i ){
sheetName = sortFilt[i];
mySheet = ss.getSheetByName(sheetName);
if(ss.getRange(sheetName "!GH3").getValue() == "Protected"){
if(mySheet.isSheetHidden() == false){
actvSheets.push(sheetName);
} else {
hidnSheets.push(sheetName);
}
}
}
msgDash("Gathering active sheets . . . ")
/** WRITING DATA FROM ACTVSHEETS ARRAY */
for(var j = 0; j < actvSheets.length; j ){
sheetName = actvSheets[j];
mySheet = ss.getSheetByName(actvSheets[j]);
sheetLink = sheetURL '#gid=' mySheet.getSheetId();
richValue = SpreadsheetApp.newRichTextValue()
.setText(sheetName)
.setLinkUrl(sheetLink)
.build();
cellID = '9060DASH!D' counter;
counter = counter 1;
ss.getRange(cellID).setRichTextValue(richValue);
}
msgDash("Gathering hidden sheets . . . ");
/** WRITING DATA FROM HDDNSHEETS ARRAY */
counter = 4;
for(var k = 0; k< hidnSheets.length; k ){
sheetName = hidnSheets[k];
mySheet = ss.getSheetByName(hidnSheets[k]);
sheetLink = sheetURL '#gid=' mySheet.getSheetId();
richValue = SpreadsheetApp.newRichTextValue()
.setText(sheetName)
.setLinkUrl(sheetLink)
.build();
cellID = '9060DASH!E' counter;
counter = counter 1;
ss.getRange(cellID).setRichTextValue(richValue);
}
msgDash('Dashboard ready. Enjoy your census work!');
ss.getRange('9060DASH!D2').setValue("For hidden sheets, respond to 'Unhide' instruction. Click Refresh button to rebuild list.");
Utilities.sleep(20);
msgDash(''); //clears messages
}