为什么copyTo（... PASTE_VALUES）不能在宏的中间工作？ [英] Why doesn't copyTo(... PASTE_VALUES) work in the middle of a macro?

问题描述

我使用电子表格的长期技术之一就是复制/粘贴特殊值（C / PSV）。使用公式来生成我感兴趣的值，IC / PSV，然后可以删除源数据。

所以我写了一个使用这种技术的宏，但是细胞空了。但是如果我把宏分成两部分，在C / PSV之前结束第一个宏，那么一切都按预期工作。为什么是这样？有没有更好的方法来解决这个问题？这是我的三个宏。

  function Step1（）{
 var spreadsheet = SpreadsheetApp.getActive（）; 
 var range = spreadsheet.getActiveRange（）; 
 CopyRangeToNewSheet（电子表格，范围）; 
 spreadsheet.getCurrentCell（）。offset（-1，6）.activate（）; 
 FillInHeaders（电子表格）; 
 spreadsheet.getCurrentCell（）。offset（1，-4）.activate（）; 
 FillInFormulas（电子表格）; 
 spreadsheet.getCurrentCell（）。offset（0，-4，range.getNumRows（），5）.activate（）; 
 spreadsheet.getCurrentCell（）。offset（0，0，1，5）.copyTo（spreadsheet.getActiveRange（），SpreadsheetApp.CopyPasteType.PASTE_NORMAL，false）; 
}; 
 
函数Step2（）{
 var spreadsheet = SpreadsheetApp.getActive（）; 
 var keepers = spreadsheet.getRange（'G：J'）; 
 keepers.activate（）; 
 keepers.copyTo（keepers，SpreadsheetApp.CopyPasteType.PASTE_VALUES，false）; 
 var discard = spreadsheet.getRange（'A：F'）
 discard.activate（）; 
 spreadsheet.getActiveSheet（）。deleteColumns（discard.getColumn（），discard.getNumColumns（））; 
}; 
 
函数BothSteps（）{
 var spreadsheet = SpreadsheetApp.getActive（）; 
 var range = spreadsheet.getActiveRange（）; 
 CopyRangeToNewSheet（电子表格，范围）; 
 spreadsheet.getCurrentCell（）。offset（-1，6）.activate（）; 
 FillInHeaders（电子表格）; 
 spreadsheet.getCurrentCell（）。offset（1，-4）.activate（）; 
 FillInFormulas（电子表格）; 
 spreadsheet.getCurrentCell（）。offset（0，-4，range.getNumRows（），5）.activate（）; 
 spreadsheet.getCurrentCell（）。offset（0，0，1，5）.copyTo（spreadsheet.getActiveRange（），SpreadsheetApp.CopyPasteType.PASTE_NORMAL，false）; 
 var keepers = spreadsheet.getRange（'G：J'）; 
 keepers.activate（）; 
 keepers.copyTo（keepers，SpreadsheetApp.CopyPasteType.PASTE_VALUES，false）; 
 var discard = spreadsheet.getRange（'A：F'）
 discard.activate（）; 
 spreadsheet.getActiveSheet（）。deleteColumns（discard.getColumn（），discard.getNumColumns（））; 
}; 
 
函数FillInHeaders（电子表格）{
 spreadsheet.getCurrentCell（）。setValue（'First Name'）; 
 spreadsheet.getCurrentCell（）。offset（0，1）.activate（）; 
 spreadsheet.getCurrentCell（）。setValue（'Last Name'）; 
 spreadsheet.getCurrentCell（）。offset（0，1）.activate（）; 
 spreadsheet.getCurrentCell（）。setValue（'Middle Name'）; 
 spreadsheet.getCurrentCell（）。offset（0，1）.activate（）; 
 spreadsheet.getCurrentCell（）。setValue（'Email'）; 
} 
 
函数FillInFormulas（电子表格）{
 spreadsheet.getCurrentCell（）。setFormulaR1C1（'= find（，R [0] C [-2]）'） ; 
 spreadsheet.getCurrentCell（）。offset（0，1）.activate（）; 
 spreadsheet.getCurrentCell（）。setFormulaR1C1（'= if（iserr（R [0] C [-1]），R [0] C [-3]，mid（R [0] C [-3] ，1，R [0] C [-1]））'）; 
 spreadsheet.getCurrentCell（）。offset（0，1）.activate（）; 
 spreadsheet.getCurrentCell（）。setFormulaR1C1（'= if（iserr（R [0] C [-2]），，mid（R [0] C [-4]，R [0] C [ -2] +1,50））'）; 
 spreadsheet.getCurrentCell（）。offset（0，2）.activate（）; 
 spreadsheet.getCurrentCell（）。setFormulaR1C1（'= R [0] C [-5]'）; 
 
 
函数CopyRangeToNewSheet（电子表格，范围）{
 var newSheet = spreadsheet.insertSheet（1）; 
 spreadsheet.setActiveSheet（newSheet，true）; 
 spreadsheet.getCurrentCell（）。offset（1，0）.activate（）; 
 range.copyTo（spreadsheet.getActiveRange（），SpreadsheetApp.CopyPasteType.PASTE_NORMAL，false）; 
 
 
 
 
 
 $ b 
这里是电子表格本身，包含标签Main，Step1，Step2的结果，和结合步骤：
  https：// docs。 google.com/spreadsheets/d/1_nabq_mHuegz_eMIPPAlIgonv71Jh6OPi6qKzeNGGTI/edit?usp=sharing  
解决方案
   SpreadsheetApp.flush（） 可能是您的宏中缺失的一步。基本上，Apps脚本优化了读取&在内部编写，如果你不调用这个方法，它可以自由地执行任务。
 
 
 
在当前将任务分离到宏1和宏2应该解决这个问题： 
 
 
  ... 
 spreadsheet.getCurrentCell（）。offset（0 ，0,1,5）.copyTo（spreadsheet.getActiveRange（），SpreadsheetApp.CopyPasteType.PASTE_NORMAL，false）; 
 //强制公式计算并等待写入。 
 SpreadsheetApp.flush（）; 
 //读取公式结果并保存为值。 
 var keepers = spreadsheet.getRange（'G：J'）; 
 ... 
  
另一种方法是将脚本从事务性通过使用 setValues（）而不是 copyTo ：
  ... 
 SpreadsheetApp.flush（）; 
 var toKeep = spreadsheet.getRange（'G：J'）; 
 toKeep.setValues（toKeep.getValues（））; 
 toKeep.getSheet（）。deleteColumns（1，toKeep.getColumn（） -  1）; 
} 
  
请注意，您仍然希望调用 .flush （）。
 
One of my longstanding techniques with spreadsheets is Copy / Paste Special Values (C/PSV), in place.  Having used formulas to produce the values I'm interested in, I C/PSV and can then delete the source data.

So I wrote a macro which uses this technique, but the cells wind up empty.  But if I split the macro into two, ending the first macro before C/PSV, then everything works as intended.  Why is this?  Is there a better way to work around this problem?  Here are my three macros.
function Step1() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getActiveRange();
  CopyRangeToNewSheet(spreadsheet, range);
  spreadsheet.getCurrentCell().offset(-1, 6).activate();
  FillInHeaders(spreadsheet);
  spreadsheet.getCurrentCell().offset(1, -4).activate();
  FillInFormulas(spreadsheet);
  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();
  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

function Step2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var keepers = spreadsheet.getRange('G:J');
  keepers.activate();
  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var discard = spreadsheet.getRange('A:F')
  discard.activate();
  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());
};

function BothSteps() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getActiveRange();
  CopyRangeToNewSheet(spreadsheet, range);
  spreadsheet.getCurrentCell().offset(-1, 6).activate();
  FillInHeaders(spreadsheet);
  spreadsheet.getCurrentCell().offset(1, -4).activate();
  FillInFormulas(spreadsheet);
  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();
  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  var keepers = spreadsheet.getRange('G:J');
  keepers.activate();
  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var discard = spreadsheet.getRange('A:F')
  discard.activate();
  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());
};

function FillInHeaders(spreadsheet) {
  spreadsheet.getCurrentCell().setValue('First Name');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setValue('Last Name');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setValue('Middle Name');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setValue('Email');
}

function FillInFormulas(spreadsheet) {
  spreadsheet.getCurrentCell().setFormulaR1C1('=find(" ",R[0]C[-2])');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-1]),R[0]C[-3],mid(R[0]C[-3],1,R[0]C[-1]))');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-2]),"",mid(R[0]C[-4],R[0]C[-2]+1,50))');
  spreadsheet.getCurrentCell().offset(0, 2).activate();
  spreadsheet.getCurrentCell().setFormulaR1C1('=R[0]C[-5]');
}

function CopyRangeToNewSheet(spreadsheet, range) {
  var newSheet = spreadsheet.insertSheet(1);
  spreadsheet.setActiveSheet(newSheet, true);  
  spreadsheet.getCurrentCell().offset(1, 0).activate();
  range.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}
Here is the spreadsheet itself, with tabs Main, Result of Step1, Step2, and Result of Combined Steps:
https://docs.google.com/spreadsheets/d/1_nabq_mHuegz_eMIPPAlIgonv71Jh6OPi6qKzeNGGTI/edit?usp=sharing
解决方案
SpreadsheetApp.flush() is likely the missing step in your macro. Basically, Apps Script optimizes reads & writes internally, and if you don't call this method, it is free to do things its way.

Adding this where you currently separate your task into "Macro 1" and "Macro 2" should resolve the issue:
...
  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  // Force formulas to calculate and pending writes to be written.
  SpreadsheetApp.flush();
  // Read formula results and save as values.
  var keepers = spreadsheet.getRange('G:J');
...
An additional method would be to condense your scripts from the "transactional" approach of a recorded macro, to the batch / efficient "big picture" view, by using setValues() instead of copyTo:
...
  SpreadsheetApp.flush();
  var toKeep = spreadsheet.getRange('G:J');
  toKeep.setValues(toKeep.getValues());
  toKeep.getSheet().deleteColumns(1, toKeep.getColumn() - 1);
}
Note that you still want the call to .flush().

                        
这篇关于为什么copyTo（... PASTE_VALUES）不能在宏的中间工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！