google-apps-script - 复制行 Google 表格
问题描述
此脚本不适用于以下文件
当第 21 列中的值 =Delivered 时,我使用此脚本将行移动到另一张表
我不是程序员我试图了解脚本如何工作并将代码嵌入其他代码
所以如果你能帮助我重写这个脚本更快更好地工作
提前致谢
/*
**** Move a row onEdit determined by a specific Cell value***
*/
// Names of sheets
var destinationSheet = "Collate"
/* col: the column to watch,
* changeVal: what value you want to change,
* del: do you want to delete after the change?
*/
var check = {
"col":17,
"changeVal": "DELIVERED",
"del": true ,
};
/* What you want to paste into the other sheet.
* start: start column
* cols: how many columns you want to copy
*/
var pasteRange = {
"start": 1,
"cols": 35
};
function onEdit(e) {
var sheets = ['FOOD', 'PET', 'KIND', 'Campbell', 'Other Clients']
for (var i = 0; i < sheets.length; i++) {
var sheetName = sheets[i]
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (sheet != null) {
if(sheet.getName() === sheets){
//Get active cell
var cell = sheet.getActiveCell();
var cellCol = cell.getColumn();
var cellRow = cell.getRow();
if(cellCol === check.col){
if(cell.getValue() === check.changeVal){
//Select the range you want to export
var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);
//Select the past destination
var pasteDestination = ss.getSheetByName(destinationSheet);
var pasteEmptyBottomRow = pasteDestination.getLastRow() + 1;
//Copy the row to the new destination
exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
SpreadsheetApp.CopyPasteType.PASTE_VALUES);
//If delete is true delete after copying
if(check.del){
sheet.deleteRow(cellRow);
};
};
};
};
};
};
};
解决方案
您可以从事件对象 [1] 中获取信息,例如已编辑的范围或绑定的电子表格。在范围使用getSheet()
函数 [2] 上,并使用 [3] 将工作表名称与数组中的工作表名称进行比较indexOf()
。如果将值“DELIVERED”放在sheets
数组中任何工作表的 Q(第 17 列)中,则以下代码移动该行:
/*
**** Move a row onEdit determined by a specific Cell value***
*/
// Names of sheets
var destinationSheet = "Collate"
/* col: the column to watch,
* changeVal: what value you want to change,
* del: do you want to delete after the change?
*/
var check = {
"col":17,
"changeVal": "DELIVERED",
"del": true ,
};
/* What you want to paste into the other sheet.
* start: start column
* cols: how many columns you want to copy
*/
var pasteRange = {
"start": 1,
"cols": 35
};
function onEdit(e) {
var sheets = ['FOOD', 'PET', 'KIND', 'Campbell', 'Other Clients'];
var sheet = e.range.getSheet();
var sheetName = sheet.getSheetName();
if(sheets.indexOf(sheetName) != -1){
//Get active cell
var cell = e.range;
var cellCol = cell.getColumn();
var cellRow = cell.getRow();
if(cellCol === check.col){
if(cell.getValue() === check.changeVal){
//Select the range you want to export
var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);
//Select the past destination
var pasteDestination = e.source.getSheetByName(destinationSheet);
var pasteEmptyBottomRow = pasteDestination.getLastRow() + 1;
//Copy the row to the new destination
exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
SpreadsheetApp.CopyPasteType.PASTE_VALUES);
//If delete is true delete after copying
if(check.del){
var sheet = e.range.getSheet();
sheet.deleteRow(cellRow);
};
};
};
};
};
在您的示例电子表格中,绑定了 2 个项目。此外,在每个项目中都有 2 个具有相同变量的脚本,这会导致问题。确保只有一个 onEdit() 函数和一个脚本,其中包含我在测试时放置的代码。
[1] https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
[2] https://developers.google.com/apps-script/reference/spreadsheet/range#getsheet
推荐阅读
- react-native - 拉刷新反应原生?
- vue.js - Vue.js - lodash | 如何在全局范围内仅导入特定功能?
- sql - 在 SQL Server 中根据时间范围计算工资
- c# - 自动法。带参数的 IoC 容器。最佳实践
- google-cloud-firestore - Firestore 安全规则 - 如何计算存在、获取、获取后的读取?
- r - 重新编码多个缺失值
- discord.js - 如何在 discord.js 中通过 id 查找用户
- java - Spring Boot中的多模块依赖管理问题
- java - 将spring boot maven项目作为依赖添加到另一个项目(本地)
- azure-devops - 从 Azure DevOps Server 迁移到 Azure DevOps Services - Backlogs 和 git repo