Move Non-Active google sheet with App Script

Question

Is there a way to move a NON active google sheet to a specific position or to insert it into a specific position with Google App Script.

Yes I could make it the active sheet and move it that way I am aware but I want to avoid that! This is the documentation that I can find on that: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#moveActiveSheet(Integer)

The issue is that because the script programmatically creates the sheet, names it and fills in a bunch of data I do not want to have to make it the active sheet. It seems needless to make the sheet the active sheet just so that I can move its position to where I want it. At no point do I ever call ActiveSheet in the entire script and would like to keep it that way.

If there is a way please let me know! I am afraid that the answer is going to be no as I cannot find a way to do it.

This is the code that I am using to create a sheet but have no way to move it around:

var sheet = SpreadsheetApp.openById('MySheetIDHere')
var tempsheet = sheet.getSheetByName('SheetTemp');
tempsheet.copyTo(sheet).setName('New Sheet Name');

Answer 1

You could try the insertSheet(sheetName, sheetIndex, options) method.

var sheet = SpreadsheetApp.openById('MySheetIDHere')
var tempsheet = sheet.getSheetByName('SheetTemp');
var pos = 3;
sheet.insertSheet('New Sheet Name', pos, {template: tempsheet});

pos is "The index of the newly created sheet. To insert a sheet as the first one in the spreadsheet, set it to 0."

If you run this script with any of the other sheets active, the script makes the copy and places it at pos but does not activate either tempsheet or the newly copied sheet.

Is this what you need?

Answer 2

Solution

Through the native SpreadsheetApp Class, this operation is not achievable without activating a Sheet.

You can use the traditional Spreadsheet API though. Activate the Advanced Spreadsheet Service going to Resources > Advanced Google Services > Sheets API. In this example I will use the version 4.

In order to achieve your goal I would use an UpdateSheetProperties request that will modify its index to any available one.

From:
Sheet1(Active), Sheet2, Sheet3

To:
Sheet1(Active), Sheet3, Sheet2

First, I will save the sheet_id of the sheet I want to move.

// In this case I will select the sheet in position 2 (index 1)
let sheets = Sheets.Spreadsheets.get("spreadsheet-id").sheets;
let sheet1_id = sheets[1].properties.sheetId;

No It's time to prepare the request:

// I will use the built-in classes to emulate the proper JSON object described in the documentation.
let batch = Sheets.newBatchUpdateSpreadsheetRequest();
let request = Sheets.newUpdateSheetPropertiesRequest();
request.fields = "index"; // This specifies I want to update only the index (position)
let properties = Sheets.newSheetProperties();
properties.sheetId = sheet1_id; // This selects the Sheet I want to update
properties.index = 3; // This is the new index (position) I want to move my Sheet to. This request will shift all the others Sheets accordingly. (Bear in mind that if it's current index the update request will be ignored)
request.properties = properties;

Now it's time to build the batch Request object and to actually perform the API call:

batch.requests = [{
    "updateSheetProperties": request
}];
Sheets.Spreadsheets.batchUpdate(batch, "spreadsheet-id");
}

Reference

Spreadsheet API v4

Advanced Google Services