javascript - 在 Google 表格中复制/粘贴的更快方法？
问题描述
我正在使用一张表为我们使用的材料创建标签，我设置了一张表来询问需要多少标签，然后制作多个副本，然后推入标签表布局之后的选项卡中。
到目前为止，我有一个脚本可以从示例标签表中获取每个单元格，然后将其复制到示例标签干净表中，然后删除任何空白行和空白单元格并向上移动。这需要很长时间，感觉这不是解决问题的最佳方法。您推荐的以最少的运行时间解决此问题的方法是什么？
以下是我现在拥有的脚本供参考：
var spreadsheet = SpreadsheetApp.openById("xxxxxxxxxxxxxxx").getSheetByName("Example Labels Clean");
spreadsheet.getRange('A:C').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('A1').activate();
spreadsheet.getRange('Labels!A1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C1').activate();
spreadsheet.getRange('Labels!A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A2').activate();
spreadsheet.getRange('Labels!A3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C2').activate();
spreadsheet.getRange('Labels!A4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('Labels!A5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C3').activate();
spreadsheet.getRange('Labels!A6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A4').activate();
spreadsheet.getRange('Labels!A7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C4').activate();
spreadsheet.getRange('Labels!A8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A5').activate();
spreadsheet.getRange('Labels!A9').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C5').activate();
spreadsheet.getRange('Labels!A10').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A6').activate();
spreadsheet.getRange('Labels!A11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C6').activate();
spreadsheet.getRange('Labels!A12').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A7').activate();
spreadsheet.getRange('Labels!A13').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C7').activate();
spreadsheet.getRange('Labels!A14').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A8').activate();
spreadsheet.getRange('Labels!A15').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A9').activate();
spreadsheet.getRange('Labels!A16').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C9').activate();
spreadsheet.getRange('Labels!A17').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A10').activate();
spreadsheet.getRange('Labels!A18').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C10').activate();
spreadsheet.getRange('Labels!A19').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A11').activate();
spreadsheet.getRange('Labels!A20').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C11').activate();
spreadsheet.getRange('Labels!A21').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A12').activate();
spreadsheet.getRange('Labels!A22').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C12').activate();
spreadsheet.getRange('Labels!A23').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A13').activate();
spreadsheet.getRange('Labels!A24').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C13').activate();
spreadsheet.getRange('Labels!A25').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A14').activate();
spreadsheet.getRange('Labels!A26').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C14').activate();
spreadsheet.getRange('Labels!A27').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A15').activate();
spreadsheet.getRange('Labels!A28').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C15').activate();
spreadsheet.getRange('Labels!A29').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A16').activate();
spreadsheet.getRange('Labels!A30').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C16').activate();
spreadsheet.getRange('Labels!B1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A17').activate();
spreadsheet.getRange('Labels!B2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C17').activate();
spreadsheet.getRange('Labels!B3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A18').activate();
spreadsheet.getRange('Labels!B4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C18').activate();
spreadsheet.getRange('Labels!B5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A19').activate();
spreadsheet.getRange('Labels!B6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C19').activate();
spreadsheet.getRange('Labels!B7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A20').activate();
spreadsheet.getRange('Labels!B8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C20').activate();
spreadsheet.getRange('Labels!B9').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A21').activate();
spreadsheet.getRange('Labels!B10').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C21').activate();
spreadsheet.getRange('Labels!B11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A22').activate();
spreadsheet.getRange('Labels!B12').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C22').activate();
spreadsheet.getRange('Labels!B13').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A23').activate();
spreadsheet.getRange('Labels!B14').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C23').activate();
spreadsheet.getRange('Labels!B15').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A24').activate();
spreadsheet.getRange('Labels!B16').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C24').activate();
spreadsheet.getRange('Labels!B17').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A25').activate();
spreadsheet.getRange('Labels!B18').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C25').activate();
spreadsheet.getRange('Labels!B19').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A26').activate();
spreadsheet.getRange('Labels!B20').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C26').activate();
spreadsheet.getRange('Labels!B21').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A27').activate();
spreadsheet.getRange('Labels!B22').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C27').activate();
spreadsheet.getRange('Labels!B23').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A28').activate();
spreadsheet.getRange('Labels!B24').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C28').activate();
spreadsheet.getRange('Labels!B25').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A29').activate();
spreadsheet.getRange('Labels!B26').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C29').activate();
spreadsheet.getRange('Labels!B27').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A30').activate();
spreadsheet.getRange('Labels!B28').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C30').activate();
spreadsheet.getRange('Labels!B29').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A31').activate();
spreadsheet.getRange('Labels!B30').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C31').activate();
spreadsheet.getRange('Labels!C1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A32').activate();
spreadsheet.getRange('Labels!C2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C32').activate();
spreadsheet.getRange('Labels!C3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A33').activate();
spreadsheet.getRange('Labels!C4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C33').activate();
spreadsheet.getRange('Labels!C5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A34').activate();
spreadsheet.getRange('Labels!C6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C34').activate();
spreadsheet.getRange('Labels!C7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A35').activate();
spreadsheet.getRange('Labels!C8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C35').activate();
spreadsheet.getRange('Labels!C9').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A36').activate();
spreadsheet.getRange('Labels!C10').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C36').activate();
spreadsheet.getRange('Labels!C11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A37').activate();
spreadsheet.getRange('Labels!C12').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C37').activate();
spreadsheet.getRange('Labels!C13').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A38').activate();
spreadsheet.getRange('Labels!C14').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C38').activate();
spreadsheet.getRange('Labels!C15').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A39').activate();
spreadsheet.getRange('Labels!C16').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C39').activate();
spreadsheet.getRange('Labels!C17').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A40').activate();
spreadsheet.getRange('Labels!C18').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C40').activate();
spreadsheet.getRange('Labels!C19').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A41').activate();
spreadsheet.getRange('Labels!C20').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C41').activate();
spreadsheet.getRange('Labels!C21').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A42').activate();
spreadsheet.getRange('Labels!C22').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C42').activate();
spreadsheet.getRange('Labels!C23').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A43').activate();
spreadsheet.getRange('Labels!C24').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C43').activate();
spreadsheet.getRange('Labels!C25').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A44').activate();
spreadsheet.getRange('Labels!C26').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C44').activate();
spreadsheet.getRange('Labels!C27').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A45').activate();
spreadsheet.getRange('Labels!C28').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('C45').activate();
spreadsheet.getRange('Labels!C29').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A46').activate();
spreadsheet.getRange('Labels!C30').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
var sh = SpreadsheetApp.openById("xxxxxxxxxxxxx").getSheetByName("Example Labels Clean");
var data = sh.getDataRange().getValues();
var targetData = new Array();
for(n=0;n<data.length;++n)
{
if(data[n].join().replace(/,/g,'')!=''){ targetData.push(data[n])};
}
sh.getDataRange().clear();
sh.getRange(1,1,targetData.length,targetData[0].length).setValues(targetData);
var sheet = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxx").getSheetByName("Example Labels Clean");
var lastRow = sheet.getLastRow();
for (var i = 1; i < lastRow+1; i++)
{
if((sheet.getRange('A'+(lastRow-i+1)).getValue()) + ""=="")
{
sheet.getRange('A'+(lastRow-i+1)).deleteCells(SpreadsheetApp.Dimension.ROWS);
}
if((sheet.getRange('C'+(lastRow-i+1)).getValue())=="")
{
sheet.getRange('C'+(lastRow-i+1)).deleteCells(SpreadsheetApp.Dimension.ROWS);
}
}
解决方案
使用批处理操作。
您已经在程序中使用过一次批处理操作来定义和设置targetData. 在其他地方做同样的事情：
- 基本上，
copyTo()脚本顶部的所有单独调用都可以用一个getValues()then 进行setValues()。 - 最终 for 循环中的单个
getRange()、getValue()和deleteCells()调用可以转换为循环外的批处理操作。 - 如果“Example Labels Clean”是同一张表，则无需将其保存到三个单独的变量
spreadsheet、sh和sheet。
此外，activate()调用是不必要的，也会减慢执行速度。此方法对于在 UI 中选择范围很有用——可能与此脚本无关。
// This is unnecessary
spreadsheet.getRange('A1').activate();
spreadsheet.getRange('Labels!A1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
// This is preferred
spreadsheet.getRange('Labels!A1').copyTo(spreadsheet.getRange('A1'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
推荐阅读
- c# - 使用来自不同实体的 viewModel 从表单保存输入
- javascript - 如何使用 MutationObserver 获取导致 DOM 突变的函数？
- angular - Angular：在 APP_INITIALIZER 中发送 http 请求之前加载配置文件
- java - 当 try 块中发生异常时从方法调用 catch 块
- c++ - 字符串成员不适用于由分隔符分隔的文件中的字符串
- azure - 如何在我的 Azure 订阅中查找所有预览服务？
- php - 如何在 Woocommerce 中获取和显示 BACS 帐户详细信息
- python - 如何在 Python 上创建块矩阵？
- arrays - 如何创建具有教义关系、joincolumn 和 ManyToOne 的数组？
- php - 如何用我的徽标替换网站标题？