Web Applications Asked by SuperJames on November 29, 2021
I want to rearrange the sheets in my workbook. I have many sheets and moving the last one to the front is very slow with drag and drop.
Ideally I want a command that will select a sheet and move it to the first position.
Is this possible?
OR, the sheet names are “month year” perhaps they can be sorted?
Expanding on @jacob-jan-tuinstra's answer, I wanted finer control than just put at index 0
, so I implemented some changes to allow direct placement of the active sheet to the right of any other sheet by name because that seems easier than remembering/counting indices. Hope it helps.
// global
var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
// create menu entry in spreadsheet upon opening
function onOpen() {
app.createMenu('Move Sheet')
.addItem('Move Active To Right Of', 'moveactiveSheet')
.addToUi();
}
// move active sheet to position zero
function moveactiveSheet() {
var name = app.prompt('Move active sheet to the right of...', 'Insert name of sheet after which to move the active sheet', app.ButtonSet.OK_CANCEL)
.getResponseText();
var foundit = null;
sheets = ss.getSheets();
for (i=0; i<sheets.length; i++) {
if (sheets[i].getName() == name) {
foundit = sheets[i];
break;
}
}
var index = foundit.getIndex();
ss.moveActiveSheet(index + 1);
}
Answered by Resisty on November 29, 2021
Try this; it will move the sheet to the end no matter how many sheets:
ss.moveActiveSheet(ss.getSheets().length);
Answered by Steve Ager on November 29, 2021
Jacob Jan Tuinstra's script was great! And a frustration- and time-saver! (Talk about frustration when you are dragging a tab and you're almost to the spot, and then you drop it (or lose it) in a place that isn't quite right and it jumps back to where you started!)
I've added some additional script tabs to the project to work with my document which has around 85+ sheets in it. I have revised the line:
ss.moveActiveSheet(0)
to: ss.moveActiveSheet(55) to move the active sheet to the middle of my document (which is at the end of a group of specific sheets),
or: ss.moveActiveSheet(85) to move the active sheet to the end (versus the beginning) of the document.
I found if you set the end sheet number too high, the script will fail when you run the debug, so if you don't want to count how many sheets you have, just play with the number.
Thanks, Jacob!
Answered by AlliOop on November 29, 2021
This script will allow you to move the active sheet to the beginning or a pick sheet that needs to be moved.
// global
var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
// create menu entry in spreadsheet upon opening
function onOpen() {
app.createMenu('Move Sheet')
.addItem('Move Active', 'moveactiveSheet')
.addItem('Move Sheet', 'movespecificSheet')
.addToUi();
}
// move active sheet to position zero
function moveactiveSheet() {
ss.moveActiveSheet(0);
}
// select sheet number (correct for zero-based array) and move to front
function movespecificSheet() {
var int = app.prompt('Select Sheet', 'Use int. only', app.ButtonSet.OK_CANCEL)
.getResponseText();
ss.setActiveSheet(ss.getSheets()[int-1]);
ss.moveActiveSheet(0);
}
I've created an example file for you: move sheets.
Make a copy of my file or add the code under Tools > Script editor. Press the bug button to authenticate the script.
Answered by Jacob Jan Tuinstra on November 29, 2021
Unfortunately, it looks like drag-and-drop is going to be your most efficient method.
While Alt+Shift+S will open the "Sheet menu", you'll still need to use the arrow keys and Enter (or mouse click) to choose "Move right"/"Move left". Even so, you'll only move the sheet one space in either direction. There's no "Move to front" option.
There's also no way within the normal Spreadsheets interface to sort the individual sheet tabs. (That's not to say someone couldn't put together a script to do it.)
Answered by ale on November 29, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP