我试图根据单元格值将数据行移动到另一张工作表。我使用在Internet上搜索后发现的这段代码。
/**
* Moves row of data to another spreadsheet based on criteria in column 6 to sheet with same name as the value in column 4.
*/
function onEdit(e) {
// see Sheet event objects docs
// https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
var ss = e.source;
var s = ss.getActiveSheet();
var r = e.range;
// to let you modify where the action and move columns are in the form responses sheet
var actionCol = 6;
var nameCol = 4;
// Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
// Get the number of columns in the active sheet.
// -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;
// if our action/status col is changed to ok do stuff
if (e.value == "ok" && colIndex == actionCol) {
// get our target sheet name - in this example we are using the priority column
var targetSheet = s.getRange(rowIndex, nameCol).getValue();
// if the sheet exists do more stuff
if (ss.getSheetByName(targetSheet)) {
// set our target sheet and target range
var targetSheet = ss.getSheetByName(targetSheet);
var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
// get our source range/row
var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
// new sheets says: 'Cannot cut from form data. Use copy instead.'
sourceRange.copyTo(targetRange);
// ..but we can still delete the row after
s.deleteRow(rowIndex);
// or you might want to keep but note move e.g. r.setValue("moved");
}
}
}
此代码可以成功地在同一电子表格的其他选项卡上传输数据行。但是,如果我想将数据移动到不同的电子表格中,它将无法正常工作。如果要将数据行移动到其他电子表格,该如何编辑该代码,使其也能正常工作?
我需要一些建议!谢谢!!
更新1
我创建了该触发代码:
function myFunction() {
}
function createSpreadsheetOpenTrigger() {
var ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger('myFunction')
.forSpreadsheet(ss)
.onEdit()
.create();
}
所以我以许可的权限运行它
然后第二个
/**
* Moves row of data to another spreadsheet based on criteria in column 6 to sheet with same name as the value in column 4.
*/
function onEdit(e) {
// see Sheet event objects docs
// https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
var ss = e.source;
var s = ss.getActiveSheet();
var r = e.range;
// to let you modify where the action and move columns are in the form responses sheet
var actionCol = 6;
var nameCol = 4;
// Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
// Get the number of columns in the active sheet.
// -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;
// if our action/status col is changed to ok do stuff
var targetss = SpreadsheetApp.openById("1S8KcrvzjvRxrABXa7A3W7QcLsCyag7DAHnWXgBjiaMc");
if (e.value == "ok" && colIndex == actionCol) {
// get our target sheet name - in this example we are using the priority column
var targetSheet = s.getRange(rowIndex, nameCol).getValue();
// if the sheet exists do more stuff
if (ss.getSheetByName(targetSheet)) {
// set our target sheet and target range
var targetSheet = targetss.getSheetByName(targetSheet);
var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
// get our source range/row
var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
// new sheets says: 'Cannot cut from form data. Use copy instead.'
var sourceData = sourceRange.getValues();
targetRange.setValues(sourceData);
// ..but we can still delete the row after
s.deleteRow(rowIndex);
// or you might want to keep but note move e.g. r.setValue("moved");
}
}
}
卡洛斯·M(Carlos M.)所做的更改。
我再次运行该文件并获得了大权限。
我说的对吗?
更新2
function createSpreadsheetOpenTrigger() {
var ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger('myFunction')
.forSpreadsheet(ss)
.onEdit()
.create();
}
function myFunction(e) {
// see Sheet event objects docs
// https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
var ss = e.source;
var s = ss.getActiveSheet();
var r = e.range;
// to let you modify where the action and move columns are in the form responses sheet
var actionCol = 6;
var nameCol = 4;
// Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
// Get the number of columns in the active sheet.
// -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;
// if our action/status col is changed to ok do stuff
var targetss = SpreadsheetApp.openById("1S8KcrvzjvRxrABXa7A3W7QcLsCyag7DAHnWXgBjiaMc");
if (e.value == "ok" && colIndex == actionCol) {
// get our target sheet name - in this example we are using the priority column
var targetSheet = s.getRange(rowIndex, nameCol).getValue();
// if the sheet exists do more stuff
if (ss.getSheetByName(targetSheet)) {
// set our target sheet and target range
var targetSheet = targetss.getSheetByName(targetSheet);
var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
// get our source range/row
var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
// new sheets says: 'Cannot cut from form data. Use copy instead.'
var sourceData = sourceRange.getValues();
targetRange.setValues(sourceData);
// ..but we can still delete the row after
s.deleteRow(rowIndex);
// or you might want to keep but note move e.g. r.setValue("moved");
}
}
}
我保存并运行它,但是它不起作用。我在做什么错?
更新3
那是原始的电子表格
那就是我希望该行数据去的电子表格。
那就是我在原始电子表格的脚本编辑器中编写的脚本。
这是我单击运行时的日志结果
它没有显示任何错误,但是该行未在其他电子表格中传输。我的代码有错误吗?
样本:
function createSpreadsheetOpenTrigger() {
var ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger('myFunction')
.forSpreadsheet(ss)
.onEdit()
.create();
}
手动运行此功能以创建触发器。
SpreadsheetApp.openById()
打开你的目标的电子表格。然后,您可以定义与源电子表格类似的范围。样本:
function myFunction(e) {
// see Sheet event objects docs
// https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
var ss = e.source;
var s = ss.getActiveSheet();
var r = e.range;
// to let you modify where the action and move columns are in the form responses sheet
var actionCol = 6;
var nameCol = 4;
// Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
// Get the number of columns in the active sheet.
// -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;
// if our action/status col is changed to ok do stuff
var targetss = SpreadsheetApp.openById("TARGET_SS_ID_HERE");
if (e.value == "ok" && colIndex == actionCol) {
// get our target sheet name - in this example we are using the priority column
var targetSheet = s.getRange(rowIndex, nameCol).getValue();
// if the sheet exists do more stuff
if (targetss.getSheetByName(targetSheet)) {
// set our target sheet and target range
var targetSheet = targetss.getSheetByName(targetSheet);
var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
// get our source range/row
var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
// new sheets says: 'Cannot cut from form data. Use copy instead.'
var sourceData = sourceRange.getValues();
targetRange.setValues(sourceData);
// ..but we can still delete the row after
s.deleteRow(rowIndex);
// or you might want to keep but note move e.g. r.setValue("moved");
}
}
}
使用此链接中的ID替换TARGET_SS_ID_HERE:https : //docs.google.com/spreadsheets/d/ < ss_ID > / edit#gid = 0
将F列的“ ok”设置为两行后:
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句