我需要根据列值将Google工作表拆分为多个选项卡(工作表)

Question

我已经搜索了许多可能的答案，但似乎找不到一个有效的答案。我有一个谷歌表单，大约有1600行，我需要根据标题为"房间"的列中的值将其拆分为大约70个不同的选项卡(每个选项卡中大约有20-30行(。我一直在排序，然后剪切和粘贴，但对于70多个标签来说，这是非常乏味的。

我可以使用Query函数，但我仍然需要创建一个新的选项卡，粘贴函数并更新特定选项卡的参数

这个脚本看起来很接近：

ss = SpreadsheetApp.getActiveSpreadsheet();
itemName = 0;
itemDescription = 1;
image = 2;
purchasedBy = 3;
cost = 4;
room = 5;
isSharing = 6;
masterSheetName = "Master";
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Update Purchases')
.addItem('Add All Rows To Sheets', 'addAllRowsToSheets')
.addItem('Add Current Row To Sheet', 'addRowToNewSheet')
.addToUi();
}
function addRowToNewSheet() {
var s = ss.getActiveSheet();
var cell = s.getActiveCell();
var rowId = cell.getRow();
var range = s.getRange(rowId, 1, 1, s.getLastColumn());
var values = range.getValues()[0];
var roomName = values[room];
appendDataToSheet(s, rowId, values, roomName);
}
function addAllRowsToSheets(){
var s = ss.getActiveSheet();
var dataValues = s.getRange(2, 1, s.getLastRow()-1, s.getLastColumn()).getValues();
for(var i = 0; i < dataValues.length; i++){
var values = dataValues[i];
var rowId = 2 + i;
var roomName = values[room];
try{
appendDataToSheet(s, rowId, values, roomName);
}catch(err){};
}
}
function appendDataToSheet(s, rowId, data, roomName){
if(s.getName() != masterSheetName){
throw new Error("Can only add rows from 'Master' sheet - make sure sheet name is 'Master'");
}
var sheetNames = [sheet.getName() for each(sheet in ss.getSheets())];
var roomSheet;
if(sheetNames.indexOf(roomName) > -1){
roomSheet = ss.getSheetByName(roomName);
var rowIdValues = roomSheet.getRange(2, 1, roomSheet.getLastRow()-1, 1).getValues();
for(var i = 0; i < rowIdValues.length; i++){
if(rowIdValues[i] == rowId){
throw new Error( data[itemName] + " from row " + rowId + " already exists in sheet " + roomName + ".");
return;
}
}
}else{
roomSheet = ss.insertSheet(roomName);
var numCols = s.getLastColumn();
roomSheet.getRange(1, 1).setValue("Row Id");
s.getRange(1, 1, 1, numCols).copyValuesToRange(roomSheet, 2, numCols+1, 1, 1);
}
var rowIdArray = [rowId];
var updatedArray = rowIdArray.concat(data);
roomSheet.appendRow(updatedArray);
}

但我总是在第51或52行遇到一个意外的令牌错误：

var sheetNames = [sheet.getName() for each(sheet in ss.getSheets())]; 

(很明显，列名等不一定适合我的数据，我试着更改它们以匹配我需要的。不确定这是否是问题的一部分。(

以下是我的数据示例：https://docs.google.com/spreadsheets/d/1kpD88_wEA5YFh5DMMkubsTnFHeNxRQL-njd9Mv-C_lc/edit?usp=sharing这应根据房间返回两个单独的选项卡/表格。

我显然不是程序员，也不懂Visual Basic或Java或其他任何东西。我只知道如何搜索和复制东西。。。。令人惊讶的是，我经常用它工作。

如果你能帮忙的话，让我知道你还需要什么。

Answer 1

尝试以下代码：

• "splitSheetIntoTabs"会将您的主表拆分为30行的单独表格。它将只复制内容，而不是背景颜色等
• "deleteTabsOtherThanMaster"将还原"splitSheetIntoTabs"所做的更改。此函数将有助于恢复splitSheetIntoTabs所做的更改

function splitSheetIntoTabs() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
var header = rows[0];
var contents = rows.slice(1);
var totalRowsPerSheet = 30; // This value will change no of rows per sheet

//below we are chunking the toltal row we have into 30 rows each
var contentRowsPerSheet = contents.map( function(e,i){ 
return i%totalRowsPerSheet===0 ? contents.slice(i,i+totalRowsPerSheet) : null; 
}).filter(function(e){ return e; });
contentRowsPerSheet.forEach(function(e){
//crate new sheet here
var currSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();

//append the header
currSheet.appendRow(header);

//populate the rows
e.forEach(function(val){
currSheet.appendRow(val);
});

});

}
// use this function revert the sheets create by splitSheetIntoTabs()
function deleteTabsOtherThanMaster() {
var sheetNotToDelete ='Master';
var ss = SpreadsheetApp.getActive();


ss.getSheets().forEach(function(sheet){
if(sheet.getSheetName()!== sheetNotToDelete)
{
ss.deleteSheet(sheet);
}
});
}

Answer 2

我使用了Kessy的漂亮脚本，但当数据变得非常大时，脚本超时，开始出现问题。我开始寻找减少脚本读取/写入电子表格的次数的方法(而不是一次读取/写入一行(，并找到了这篇文章https://stackoverflow.com/a/42633934

使用这一原理并更改脚本中的循环以使循环中有一个循环有助于减少这些调用。这意味着您还可以避免第二次调用追加行("else"(。我的脚本与示例有点不同，但基本上以以下内容结尾：

`for (var i = 1; i < theEmails.length; i++) {    
//Ignore blank Emails and sheets created
if (theEmails[i][0] !== "" && !completedSheets.includes(theEmails[i][0])) { 
//Set the Sheet name = email address.  Index the sheets so they appear last.
var currentSheet = theWorkbook.insertSheet(theEmails[i][0],4+i);

//append the header

//To avoid pasting formulas, we have to paste contents
headerFormat.copyTo(currentSheet.getRange(1,1),{contentsOnly:true});
//Now here find all the rows containing the same email address and append them
var theNewRows =[];
var b=0;
for(var j = 1; j < rows.length; j++)
{

if(rows[j][0] == theEmails[i][0]) {

theNewRows[b]=[];//Initial new array
theNewRows[b].push(rows[j][0],rows[j][1],rows[j][2],rows[j][3],rows[j][4],rows[j][5],rows[j][6],rows[j][7]);
b++;
}
}var outrng = currentSheet.getRange(2,1,theNewRows.length,8); //Make the output range the same size as the output array
outrng.setValues(theNewRows);

我发现一个约有1000行的表超时了，但使用新脚本需要6.5秒。它可能不是很整洁，因为我只涉猎剧本，但也许它会有所帮助。

Answer 3

我已经完成了这个脚本，它成功地获取了每个房间，并创建了一个具有相应房间名称的新表，并添加了具有相同房间的所有行。

function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
// This var will contain all the values from column C -> Room
var columnRoom = sheet.getRange("C:C").getValues();
// This var will contain all the rows
var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
//Set the first row as the header
var header = rows[0];
//Store the rooms already created
var completedRooms = []
//The last created room
var last = columnRoom[1][0]

for (var i = 1; i < columnRoom.length; i++) {    
//Check if the room is already done, if not go in and create the sheet
if(!completedRooms.includes(columnRoom[i][0])) {
//Set the Sheet name = room (except if there is no name, then = No Room)
if (columnRoom[i][0] === "") {
var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("No Room");
} else {
var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(columnRoom[i][0]);
}

//append the header
currentSheet.appendRow(header);
currentSheet.appendRow(rows[i]);
completedRooms.push(columnRoom[i][0])
last = columnRoom[i][0]
} else if (last == columnRoom[i][0]) {
// If the room's sheet is created append the row to the sheet

var currentSheet = SpreadsheetApp.getActiveSpreadsheet()
currentSheet.appendRow(rows[i]);
}
}
}

请测试它，并毫不犹豫地评论改进。