我需要根据列值将Google表格拆分为多个标签(表格) [英] I need to split a Google Sheet into multiple tabs (sheets) based on column value

问题描述

我搜索了许多可能的答案，但似乎找不到有效的答案.我有一个包含约1600行的Google表格，我需要根据房间"列中的值将其拆分为约70个不同的标签(每个标签中约有20-30行).我一直在进行排序，然后剪切和粘贴，但是对于70多个标签，这非常繁琐.

I have searched many possible answers but cannot seem to find one that works. I have a Google Sheet with about 1600 rows that I need to split into about 70 different tabs (with about 20-30 rows in each one) based on the value in the column titled "room". I have been sorting and then cutting and pasting but for 70+ tabs this is very tedious.

我可以使用Query函数，但是我仍然需要创建一个新标签页，粘贴该函数并更新该特定标签页的参数.

I can use the Query function but I still need to create a new tab, paste the function and update the parameter for that particular tab.

此脚本似乎非常接近:

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行出现意外的令牌错误:

But I always get an unexpected token error on line 51 or 52:

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

(显然，列名等不一定适合我的数据，我尝试更改它们以使其与所需内容相匹配.不确定这是否是问题的一部分.)

(And obviously the column names, etc. are not necessarily correct for my data, I tried changing them to match what I needed. Not sure if that was part of the issue.)

以下是我的数据示例: https ://docs.google.com/spreadsheets/d/1kpD88_wEA5YFh5DMMkubsTnFHeNxRQL-njd9Mv-C_lc/edit?usp = sharing 这应该基于room返回两个单独的标签/工作表.

Here is a sample of my data: https://docs.google.com/spreadsheets/d/1kpD88_wEA5YFh5DMMkubsTnFHeNxRQL-njd9Mv-C_lc/edit?usp=sharing This should return two separate tabs/sheets based on room .

我显然不是程序员，也不了解Visual Basic或Java或任何东西.我只知道如何搜索和复制内容....我常常能使它正常工作.

I am obviously not a programmer and do not know Visual Basic or Java or anything. I just know how to google and copy things....amazingly I often get it to work.

如果您需要帮助，请让我知道您还需要什么.

Let me know what else you need if you can help.

推荐答案

我完成了此脚本，该脚本成功获取了每个房间，并使用相应的房间名称创建了一个新工作表，并添加了具有相同房间的所有行.

I have done this script that successfully gets each room and creates a new sheet with the corresponding room name and adding all the rows with the same room.

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]);
    }

  }

}

请对其进行测试，请随时评论改进之处.

Please test it and don't hesitate to comment for improvements.

这篇关于我需要根据列值将Google表格拆分为多个标签(表格)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！