Question

0

152

Views

How to set the newly created sheet as the active sheet? Google sheets

Am having a problem that I have to run each script alone due to not being able to switch to the newly created sheet generated by the function copyWithProtections, so when the next function ClearValueAftercreatingnewsheet runs, it runs on the active sheet not the newly generated one, is there a way to to have the newly created sheet as the active one?

/* CAUTION: COPY WITH PROTECTION SHOULD BE RUNNED FIRST THEN CLEARVALUEAFTERCREATING NEW SHEET AFTER MAKING SURE THAT YOU MANUALLY CHANGED THE ACTIVE SHEET TO THE NEW SHEET WITH THE NUMBER */

//Copies with protection

function copyWithProtections(){
  
  const sh = SpreadsheetApp.getActiveSpreadsheet();
  const ss = sh.getSheetByName("Mar22");
  const prot = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE)
  
  let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1);
  let p;
  
  for (let i in prot){
    p = nSheet.getRange(prot[i].getRange().getA1Notation()).protect();
    p.removeEditors(p.getEditors());
    if (p.canDomainEdit()) {
      p.setDomainEdit(false);
    }

  } 
  
}
//Clears Values of new sheets

function ClearValueAftercreatingnewsheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A2:P144').activate().clear({contentsOnly: true});
  spreadsheet.getRange('Z5').activate();
  spreadsheet.getCurrentCell().setValue('');
  spreadsheet.getRange('Z8').activate();
  spreadsheet.getCurrentCell().setValue('');
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
   spreadsheet.getRange('A2:Q270').activate();
  spreadsheet.getActiveRangeList().setBackground('#deeaf6');
  spreadsheet.getRange('A2:R270').activate();
  spreadsheet.getActiveRangeList().setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.SOLID);
};

7 months ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-03T03:22:20.914Z

You can set the active sheet from the sheet name. By making your script in one single function, you can use the newly created output sheet nSheet and get it's name using getSheetName(). You can then reference it on the clear values part. Try the following code instead:

function copyAndClear() {

  //Copies with protection
  const sh = SpreadsheetApp.getActiveSpreadsheet();
  const ss = sh.getSheetByName("Mar22");
  const prot = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE)

  let nSheet = ss.copyTo(sh).setName(sh.getNumSheets() - 1);
  let p;

  for (let i in prot) {
    p = nSheet.getRange(prot[i].getRange().getA1Notation()).protect();
    p.removeEditors(p.getEditors());
    if (p.canDomainEdit()) {
      p.setDomainEdit(false);
    }
  }

  //Set the newly created sheet name in a variable to be used for reference
  var nSheetName = nSheet.getSheetName();

  //Clears Values of new sheets
  var spreadsheet = sh.getSheetByName(nSheetName);
  spreadsheet.getRange('A2:P144').activate().clear({contentsOnly: true});
  spreadsheet.getRange('Z5').setValue('');
  spreadsheet.getRange('Z8').setValue('');
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
   spreadsheet.getRange('A2:Q270').setBackground('#deeaf6');
  spreadsheet.getRange('A2:R270').setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.SOLID);
};

I have also simplified your code to avoid redundancy and make the run time shorter since based on your code there's no need for the .activate() if you can just set it directly in one line.

Let me know if this works!

You can also set the active sheet by ID but the code will be much longer. Here's the reference for setting active sheet: https://spreadsheet.dev/activate-sheet-in-google-sheets-using-google-apps-script

EDIT: I have kept the .activate() on the first line

spreadsheet.getRange('A2:P144').activate().clear({contentsOnly: true});

as for some reason if I set it directly as

spreadsheet.getRange('A2:P144').clear({contentsOnly: true});

without the .activate() it is also clearing the original sheet.