There is a script respected Hyde - link. This script copies protected sheets along with the protection. It has three different use cases. How do I leave out the part of the script that does "copy a sheet to another spreadsheet, using the same sheet name, and put it at the first position in the tab bar".

/**
* Copies a sheet, retaining protections.
* 
* The copy can be created within the same spreadsheet file as the original,
* or in another spreadsheet file.
*
* If a sheet by the name optNewSheetName already exists in the spreadsheet,
* the new sheet is named 'Copy of Template' or similar.
*
* Usage:
 
  const ss = SpreadsheetApp.getActive();
  const sheetToCopy = ss.getSheetByName('Template');
  let newSheet;
 
  // duplicate a template sheet, and put it at the last position in the tab bar
  const newSheetName = 'Data '   Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd');
  newSheet = copySheetWithProtections_(sheetToCopy, newSheetName);
 
  // copy a sheet to another spreadsheet, using the same sheet name, and put it at the first position in the tab bar
  const targetSpreadsheet = SpreadsheetApp.openById('123abc...cba321');
  newSheet = copySheetWithProtections_(sheetToCopy, undefined, 0, targetSpreadsheet);
 
  // copy sheets '1', '2' and '3' to another spreadsheet, and put them at the end of the tab bar
  const sheetsToCopy = ['1', '2', '3',];
  const newSheets = [];
  sheetsToCopy.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    newSheets.push(copySheetWithProtections_(sheet, sheetName, undefined, targetSpreadsheet));
  });
  console.log(`Copied ${newSheets.length} sheets to '${targetSpreadsheet.getName()}'.`);
 
* @param {Sheet} sheet The sheet to copy.
* @param {String} optNewSheetName Optional. Defaults to the name of the source sheet.
* @param {Number} optSheetIndex Optional. The zero-indexed position of the new sheet in the tab bar. Defaults to the last position.
* @param {String} optTargetSpreadsheet Optional. The target spreadsheet. Defaults to the current spreadsheet.
* @return {Sheet} The new copy of sheet.
*/
function copySheetWithProtections_(sheet, optNewSheetName, optSheetIndex, optTargetSpreadsheet) {
  // version 1.2, written by --Hyde, 16 February 2022
  //  - add optTargetSpreadsheet
  //  - see https://support.google.com/docs/thread/147940588
  // version 1.1, written by --Hyde, 8 February 2022
  //  - see https://support.google.com/docs/thread/149743347?msgid=149890712
  //  - see https://support.google.com/docs/thread/144274437
  //  - see https://support.google.com/docs/thread/126744993
 
  const ss = sheet.getParent();
  const newSheetName = optNewSheetName || sheet.getName();
  const targetSs = optTargetSpreadsheet || ss;
  const sheetIndex = optSheetIndex ?? targetSs.getNumSheets()   1;
  const me = Session.getEffectiveUser();
  if (!me.getEmail()) {
    throw new Error('Cannot retrieve identity of the effective user.');
  }
  const newSheet = sheet.copyTo(targetSs);
  newSheet.activate();
  targetSs.moveActiveSheet(sheetIndex);
  try {
    newSheet.setName(newSheetName);
  } catch (error) {
    ; // the sheet name is already taken, retain the default 'Copy of...' name
  }
  const sheetProt = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  if (sheetProt) {
    const newSheetProt = newSheet
      .protect()
      .setDescription(sheetProt.getDescription())
      .setWarningOnly(sheetProt.isWarningOnly());
    if (!sheetProt.isWarningOnly()) {
      newSheetProt
        .addEditor(me)
        .removeEditors(newSheetProt.getEditors().filter(user => user.getEmail() !== me))
        .addEditors(sheetProt.getEditors());
      try {
        newSheetProt.setDomainEdit(sheetProt.canDomainEdit());
      } catch (error) {
        ; // we are not in a Google Workspace domain
      }
    }
    const unprotectedRanges = sheetProt.getUnprotectedRanges()
      .map(range => newSheet.getRange(range.getA1Notation()));
    newSheetProt.setUnprotectedRanges(unprotectedRanges);
  } else {
    sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)
      .forEach(rangeProt => {
        const rangeA1 = rangeProt.getRange().getA1Notation();
        const newRangeProt = newSheet.getRange(rangeA1)
          .protect()
          .setDescription(rangeProt.getDescription())
          .setWarningOnly(rangeProt.isWarningOnly());
        if (!rangeProt.isWarningOnly()) {
          newRangeProt
            .addEditor(me)
            .removeEditors(newRangeProt.getEditors().filter(user => user.getEmail() !== me))
            .addEditors(rangeProt.getEditors());
          try {
            newRangeProt.setDomainEdit(rangeProt.canDomainEdit());
          } catch (error) {
            ; // we are not in a Google Workspace domain
          }
        }
      });
  }
  return newSheet;
}

How can I get a script out of this that does "copy a sheet to another spreadsheet, using the same sheet name, and put it at the first position in the tab bar"?

CodePudding user response：

How can I get a script out of this that does "copy a sheet to another spreadsheet, using the same sheet name, and put it at the first position in the tab bar"?

Follow the usage example in the JSDoc, like this:

function copyASheetToAnotherSpreadsheetUsingTheSameSheetNameAndPutItAtTheFirstPositionInTheTabBar() {
  const sheetToCopy = SpreadsheetApp.getActive().getSheetByName('Template');
  const targetSpreadsheet = SpreadsheetApp.openById('123abc...cba321');
  const newSheet = copySheetWithProtections_(sheetToCopy, undefined, 0, targetSpreadsheet);
  console.log(`Copied sheet '${newSheet.getName()}' to spreadsheet '${targetSpreadsheet.getName()}'.`);
}