如何使谷歌应用程序脚本中的单元格引用动态

Question

我正在为我的重复工作创建一个宏。

我想做的事：

1. 复制Sheet1中的当前单元格
2. 粘贴到图纸2
3. 底部将运行一个=filter公式，我需要导航到底部并复制输出范围
4. 使单元格返回到表2中的原始A1单元格位置
5. 将输出粘贴到Sheet3，然后向下移动一行
6. 循环它，直到它遇到Sheet1行中的一个空单元格

我面临的挑战：

• 在Sheet1中：我创建的宏只引用我第一次运行宏时使用的单元格(即使在我从不同的单元格开始之后，宏仍然复制相同的初始单元格(
• 在Sheet2中：输出可能是一行或多行输出，因此看起来在宏过程中Ctrl+a可能无法完成任务
• 对于循环：宏只允许我运行一次，但我需要它重复运行，直到它遇到Sheet1中的空单元格

挑战1和2是我的主要挑战，如果我不能让宏循环，我可以手动使用热键来运行宏，但如果有人能教我如何循环，我将不胜感激。

function CleanUp6() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
spreadsheet.getRange(''Sheet1'!C2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getActiveRange().getDataRegion().activate();
currentCell.activateAsCurrentCell();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet3'), true);
spreadsheet.getCurrentCell().offset(1, 0).activate();
spreadsheet.getRange(''Sheet2'!A8:D8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

Answer 1

使用宏记录器来尝试开发脚本不是一个好主意。它记录的是鼠标和键盘的动作，而不是代码逻辑。我想你想要的是这样的东西。不能确定，但在电子表格副本上试试。使用onOpen((触发器添加一个菜单选项。

function onOpen() {
var menu = SpreadsheetApp.getUi().createMenu("File Picker");
menu.addItem("Test","test");
menu.addToUi();
}
function test() {
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
if( sh.getName() !== "Sheet1") {
SpreadsheetApp.getUi().alert("Change to Sheet1");
return;
}
if( sh.getActiveCell().getA1Notation() !== "A1" ) {
SpreadsheetApp.getUi().alert("Change to cell A1");
return;
}
// get the values in A:A
var values = sh.getRange(1,1,sh.getLastRow(),1).getValues();
sh = ss.getSheetByName("Sheet2");
// copy values from Sheet1 to Sheet2
// assumes the formulas are outside of the range values
sh.getRange(1,1,values.length,1).setValues(values);
// get the last row
values = sh.getRange(sh.getLastRow(),1,1,sh.getLastColumn()).getValues();
sh = ss.getSheetByName("Sheet3");
// get the row following the last row .getLastRow()+1
sh.getRange(sh.getLastRow()+1,1,values.length,values[0].length).setValues(values);
}
catch(err) {
console.log(err);
}
}