是否存在Google表格的代码，该代码会将特定颜色的每个标签移到我的标签列表的末尾? [英] Is there a code for Google Sheets that will move every tab of a specific color to the end of my list of tabs?
问题描述
我有一个Google表格，其中包含100多个标签(工作表).我根据标签在工作流程中的位置对标签进行颜色编码(黄色-我正在收集数据，绿色已准备好供我的合作伙伴进行研究，蓝色-我的合作伙伴正在进行研究，进行了红死交易).我们每天都会添加多个标签，并且标签列表正在迅速增长.我的搭档希望所有无效交易(红色标签)都在列表的末尾.将选项卡一一拖到末尾会花费太长时间.我需要一个脚本(或其他任何可行的方法)来将每个标记为红色的选项卡移至选项卡列表的末尾.
I have a Google Sheet with over 100 tabs (worksheets). I color code my tabs based on where they are in my workflow (Yellow-I'm collecting data, Green-ready for my partner to research, Blue-my partner is researching, Red-dead deal). We add multiple tabs on a daily basis, and the list of tabs is growing rapidly. My partner wants all of the dead deals (red tabs) to be at the end of the list. Dragging the tabs one by one to the end takes way too long. I need a script (or any other working method) to take every tab that is colored red and move them to the end of the list of tabs.
到目前为止，我发现的唯一选择是Excel的VBA代码.据我所知，VBA不适用于Google Sheets，因为它是仅Microsoft使用SMH的代码.
So far the only option I've found was a VBA code for Excel. As far as I can tell, VBA doesn't work with Google Sheets, because it is a code that only Microsoft uses SMH.
我对jquery或脚本的了解不足以编写自己的代码，而且我无法通过Googling找到答案，因此我没有任何测试结果.另外，我的工作表中包含敏感数据，因此我不能将其作为测试数据提供，但该问题应该很容易(对于知道如何编写脚本的人来说)无需测试表即可进行复制.只需制作一个带有3个左右标签的空白Google表格，将第一个红色标记为红色，然后处理将红色标签移至末尾的脚本即可.
I don't know enough about jquery or scripting to write my own code, and I haven't been able to find an answer by Googling, so I don't have any test results. Also, my sheet has sensitive data, so I can't provide it as test data, but the question should be easy (for someone who knows how to script) to reproduce without need of a test sheet. Just make a blank Google Sheet with 3 or so tabs, color the 1st one red, then work on a script that will move that red tab to the end.
推荐答案
使用Google Sheets API版本4将一种颜色的工作表/标签移动到选项卡列表的末尾
function moveSheetsOfAColorToEnd(color) {
var color=color || '#ff0000';
if(color) {
var ss=SpreadsheetApp.getActive();
var shts=ss.getSheets();
for(var i=0;i<shts.length;i++) {
if(shts[i].getTabColor()==color) {
Sheets.Spreadsheets.batchUpdate(
{
requests:[
{
"updateSheetProperties":
{
"properties":
{
"sheetId":shts[i].getSheetId(),
"index": shts.length
},
"fields": "index"
}
}
]
}, ss.getId());
}
}
}
}
您将需要在资源"菜单以及 Google Cloud Platform .
You will need to Enable Sheets API v4 in Resources Menu and also in the Google Cloud Platform.
shts.length是从Spreadsheet.getSheets()返回的数组的长度.由于工作表索引始于一个将给定工作表索引设置为该数组的长度的位置，因此始终会将该工作表移至末尾.
shts.lengthis the length of the array returned from Spreadsheet.getSheets(). Since the sheets index begins at one setting a given sheet index to the length of that array will always move that sheet to the end.
这是做同一件事的另一种方法:
Here's another way to do the same thing:
function moveSheetsOfAColorToEnd1(color) {
var color=color || '#ff0000';
if(color) {
var ss=SpreadsheetApp.getActive();
var shts=ss.getSheets();
for(var i=0;i<shts.length;i++) {
if(shts[i].getTabColor()==color) {
var shtsPropReq=Sheets.newUpdateSheetPropertiesRequest();
shtsPropReq.properties=Sheets.newSheetProperties();
shtsPropReq.properties.sheetId=shts[i].getSheetId();
shtsPropReq.properties.index=shts.length;
shtsPropReq.fields="index";
var requests=[Sheets.newRequest()];
requests[0].updateSheetProperties=shtsPropReq;
var batchUpdateRequest=Sheets.newBatchUpdateSpreadsheetRequest();
batchUpdateRequest.requests=requests;
Sheets.Spreadsheets.batchUpdate(batchUpdateRequest, ss.getId());
}
}
}
}
此方法遵循与在此页面上找到的Apps脚本示例相同的模式.
还有一些其他示例，此处
这篇关于是否存在Google表格的代码，该代码会将特定颜色的每个标签移到我的标签列表的末尾?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
