我有一个脚本,可以根据单元格的值将Google表格行从一个工作表移动到另一个工作表。我现在要完成的是樱桃采摘列的位置。
例如,第一列中的"客户"从相应第一列中的"潜在客户"移动到商机,第二列中的">客户电话"从商机中的潜在客户移动到第五列,第三列中的"来源"只是一起跳过,然后重复以下列。
关于如何实现这一目标的任何想法?提前感谢!
这是我用于移动行的代码。
function onEdit() {
// moves a row from a sheet to another when a magic value is entered in a column
var sheetNameToWatch = "Leads";
var columnNumberToWatch = 9;
var valueToWatch = "Opportunity";
var sheetNameToMoveTheRowTo = "Opportunities";
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveCell();
if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
sheet.deleteRow(range.getRow());
}
}
"对象文本"可用于进行用户设置,以确定源和目标。
例:
function onEdit() {
var mySettings,thisSheet;//Define variables without assigning a value
// USER INPUT
mySettings = {//make settings here
"targetSheetOne":{//First sheet to get data
"columnToColumnMap":{//source column to be put in target column
"column1":"column5",//source column1 will be copied to target column5
"column2":"column2",
"column3":"column3"
}
}
}
// END OF USER INPUT
for (key1 in mySettings) {//Loop through all target sheets in settings
thisSheet = SpreadsheetApp.getSheetByName(key1);
innerObject = mySettings[key1];
for (key2 in innerObject) {
//Write column data to correct column
}
}
}