Google脚本：在同一电子表格中有条件地将行从一张表复制到另一张表 [英] Google Script: Conditionally copy rows from one sheet to another in the same spreadsheet
问题描述
我阅读了 Google脚本：有条件地复制行一个电子表格到另一个，并不能使它为我工作。
我需要一个脚本，可以让我做下面引用的文本。请随时修改我的电子表格，以完成一项完整的工作脚本。我会将电子表格公开给任何人复制脚本以供自己使用。我对剧本写作一无所知，所以我需要把它全部写出来。抱歉，我是这样的一个小菜鸟，但这会帮助我学习。
https://docs.google.com/spreadsheet/ccc?key=0AoJdwy8V1ldHdFA5M1M1Wlp1NHZhcmxJOUZKVEU4X3c&usp=sharing
如果表格All_Mileage中的列B表示John，那么我希望该行到
被复制并粘贴到从第3行开始的表格John如果列表All_Mileage中的列B表示Adam，那么我希望该行到
被复制并粘贴到SheetAdam中在第3行和后面。
如果表格All_Mileage中的列B表示Mike，那么我希望该行到
被复制并粘贴到Sheet迈克从第3行开始。继续。
我在这里看到了其他脚本，但我无法让它们工作。就像我说的，当它来到代码的时候，我比绿树更绿。
非常感谢！
C.Lang是对的，这不是一个准备好脚本的地方......但是由于这个问题非常常见，并且经常得到回答，我花了几分钟的时间来编写和测试...所以它是这样的：
$ $ p $ code var ss = SpreadsheetApp.getActiveSpreadsheet（）; //一些全局变量
var master = ss.getSheetByName（'All_Mileage'）;
var colWidth = master.getMaxColumns（）;
函数copyRowsOnCondition（）{
var data = master.getDataRange（）。getValues（）;
for（n = 2; n
Logger.log（data [n] [1]）$ b $ b var dest = ss.getSheetByName（data [n] [1] .toString（）.exit（/ / g，''））; //删除可能包含在名称中的所有空格，以确保
var destRange = dest.getRange（dest.getLastRow（）+ 1,1）; //写入地点
master.getRange（n + 1,1,1，colWidth）.copyTo（destRange）;拷贝自身的值&格式
}
} //循环
}
编辑：因为我使用MasterSheet中的名称值来查找目标工作表，我认为它可能是有用的处理目标工作表不存在的情况下创建它使用相同的规则，即。 name = sheetName ...
另一个问题是无法知道哪些行已经存在复制...所以我做了一个处理所有这些的版本，仅复制手动选择的行（即使只在一个列中），并更改背景颜色以指示已处理这些行。我还添加了一个菜单，以获得最低的舒适度; - ）
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var master = ss.getSheetByName（'All_Mileage'）;
var colWidth = master.getLastColumn（）; //上次在masterSheet中使用col
var sheets = ss.getSheets（）; //页数
函数onOpen（） {
var menuEntries = [{name：将所选行复制到工作表，functionName：copyRowsOnConditionV2}，
];
ss.addMenu（Copy functions，menuEntries）; //定制菜单
}
函数copyRowsOnConditionV2（）{
var sheetNames = []; //现有工作表数组名称
var sheets = ss.getSheets（）; //页数
（s = 0; s< sheets.length; ++ s）{sheetNames.push（sheets [s] .getName （））};
ss.getActiveSelection（）。setBackground（'＃ffffbb'）;
var selectedfirstRow = ss.getActiveSelection（）。getRowIndex（）;
var selectedHeigth = ss.getActiveSelection（）。getHeight（）
var selectedFullRange = master.getRange（selectedfirstRow，1，selectedHeigth，colWidth）;
var data = selectedFullRange.getValues（）; （n = 0; n
if（sheetNames.toString（ ）.match（data [n] [1] .toString（）。replace（/ /g,''))!=data[n][1].toString().replace(/ / g，''）） {//如果没有工作表存在这个名字
var newSheet = ss.insertSheet（data [n] [1] .toString（）。replace（/ /g,''),ss.getSheets().length ）; //然后创建它
master.getRange（1,1,2，colWidth）.copyTo（newSheet.getRange（1,1））; //并复制第2行的标题，然后继续通常
newSheet.getRange（1,1）.setValue（'Gas Mileage Log - '+ data [n] [1] .toString（）。replace（/ / g，''））; //设置名称in header
SpreadsheetApp.flush（）;
var sheets = ss.getSheets（）; //页数
var sheetNames = []; //更新现有工作表名称
（s = 1; s< sheets）。长度; ++ S）{sheetNames.push（片材[S] .getName（））};
Logger.log（sheetNames）
};
var dest = ss.getSheetByName（data [n] [1] .toString（）。replace（/ / g，''））; //找到目标工作表
Logger.log（data [ n）[1] .toString（）。replace（/ / g，''））
var destRange = dest.getRange（dest.getLastRow（）+ 1,1）; //定义范围
master.getRange（selectedfirstRow + n，1,1，colWidth）.copyTo（destRange）; //并在最后一行下面复制
}
}
}
下图：
I read Google Script: Conditionally copy rows from one spreadsheet to another and could not make it work for me.
I need a script that will allow me to do the quoted text below. Please feel free to edit my spreadsheet to make a complete working script. I will keep the spreadsheet publicly available for anyone to copy the script for their own use. I really know nothing about script writing so I need it all spelled out. Sorry, I'm such a noob with all this, but this will help me learn.
https://docs.google.com/spreadsheet/ccc?key=0AoJdwy8V1ldHdFA5M1M1Wlp1NHZhcmxJOUZKVEU4X3c&usp=sharing
If Column B on sheet "All_Mileage" says "John" then I want that row to be copy and pasted into Sheet "John" starting on row 3 and following.
If Column B on sheet "All_Mileage" says "Adam" then I want that row to be copy and pasted into Sheet "Adam" starting on row 3 and following.
If Column B on sheet "All_Mileage" says "Mike" then I want that row to be copy and pasted into Sheet "Mike" starting on row 3 and following.
I saw other scripts on here, but I couldn't get them to work. Like I said I'm greener than a sapling when it come to code.
Thanks a ton!
C.Lang is right, this is not a place to get readymade scripts ... but since this question is so common and has been aswered so often it took me a few minutes to write and test... so there it is :
var ss=SpreadsheetApp.getActiveSpreadsheet();// some global variables
var master = ss.getSheetByName('All_Mileage');
var colWidth = master.getMaxColumns();
function copyRowsOnCondition() {
var data = master.getDataRange().getValues();
for(n=2;n<data.length;++n){
if(data[n][1].length<16){ // if not pre-filled with your text
Logger.log(data[n][1])
var dest = ss.getSheetByName(data[n][1].toString().replace(/ /g,''));//remove any spaces that could be included in the name so the name = sheetName for sure
var destRange = dest.getRange(dest.getLastRow()+1,1);// the place to write
master.getRange(n+1,1,1,colWidth).copyTo(destRange);the copy itself value & format
}
}// loop
}
EDIT : since I used the name value in MasterSheet to find destination sheet I thought it might be usefull to handle the case where the destination sheet doen't exist by creating it using the same rule, ie. name = sheetName...
The other issue was that there was no way to know which rows had been already copied... so I made a version that handles all that, copying only the rows that are manually selected (even in only a single column) and change the background color to tell that these rows have been processed. I also added a menu for a minimal comfort ;-)
(how to keep busy on a cold sunday afternoon ;-)
var ss=SpreadsheetApp.getActiveSpreadsheet();
var master = ss.getSheetByName('All_Mileage');
var colWidth = master.getLastColumn();// last used col in masterSheet
var sheets = ss.getSheets();// number of sheets
function onOpen() {
var menuEntries = [ {name: "Copy selected Rows to sheets", functionName: "copyRowsOnConditionV2"},
];
ss.addMenu("Copy functions",menuEntries);// custom menu
}
function copyRowsOnConditionV2() {
var sheetNames = [];// array of existing sheet names
var sheets = ss.getSheets();// number of sheets
for(s=0;s<sheets.length;++s){sheetNames.push(sheets[s].getName())};
ss.getActiveSelection().setBackground('#ffffbb');
var selectedfirstRow = ss.getActiveSelection().getRowIndex();
var selectedHeigth = ss.getActiveSelection().getHeight()
var selectedFullRange = master.getRange(selectedfirstRow,1,selectedHeigth,colWidth);
var data = selectedFullRange.getValues();
for(n=0;n<data.length;++n){
if(data[n][1].length<16){
if(sheetNames.toString().match(data[n][1].toString().replace(/ /g,''))!=data[n][1].toString().replace(/ /g,'')){// if no sheet exist with this name
var newSheet = ss.insertSheet(data[n][1].toString().replace(/ /g,''),ss.getSheets().length);// then create it
master.getRange(1,1,2,colWidth).copyTo(newSheet.getRange(1,1));// and copy the headers on 2 first rows, then continue as usual
newSheet.getRange(1,1).setValue('Gas Mileage Log - '+data[n][1].toString().replace(/ /g,''));// set name in header
SpreadsheetApp.flush();
var sheets = ss.getSheets();// number of sheets
var sheetNames = [];// update array of existing sheet names
for(s=1;s<sheets.length;++s){sheetNames.push(sheets[s].getName())};
Logger.log(sheetNames)
};
var dest = ss.getSheetByName(data[n][1].toString().replace(/ /g,''));//find the destination sheet
Logger.log(data[n][1].toString().replace(/ /g,''))
var destRange = dest.getRange(dest.getLastRow()+1,1);// define range
master.getRange(selectedfirstRow+n,1,1,colWidth).copyTo(destRange);// and make copy below last row
}
}
}
Illustration below :
这篇关于Google脚本：在同一电子表格中有条件地将行从一张表复制到另一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
