脚本结尾的copyToRange（以及类似的）执行？ [英] copyToRange (and similar) execution at end of script?

问题描述

我正在制作一张我想通过Google Apps脚本定期更新（每周）的大型表单。
每周我需要在我的工作表的end（lastDataColumn）处添加一个新列，然后将最后两个colums（用Fomulas计算每周相对变化）移动到end（即将它们移动一个列向右）。这给我留下了一个用lastDataColumn - 2寻址的空白列。这是新报告数据的去向。

我有两个函数。
copyCols和getReports。

它们都能正常工作，所以copyCols使用上面解释的方法在位置lastDataColumn - 2处创建一个新的空列， getReports从Analytics，第三方API和其他工作表中获取报表数据，然后将这些值写入lastDataColumn - 2位置的列中。然而，如果将这两个函数分组在让我们说我的主要功能，然后我想在7天的基础上触发，copyCols似乎只执行到创建一个新的空列的点。然后getReports完全执行并将所有数据写入lastDataColumn - 2中。但是没有列被移动，所以getReports覆盖上周的数据。从getReports执行所有事情后，copyCols开始移动行（即复制）。
这给我留下了lastDataColumn - 3的重复列（它应该有最后一周的数据，但是被这个星期的数据覆盖，因为它仍在lastDataColumn - 2中，在执行getReports之前）在lastDataColumn - 2中。

澄清：事后执行copyCols和getReports（每个都是它自己的）完美无瑕。

Google Apps脚本是无线的吗？如果是这样，为什么描述的问题会发生？在脚本的末尾执行批量操作（如复制范围）吗？

代码：

  var today = new Date（）; 
 var start = new Date（today.getTime（） -  7 * 24 * 60 * 60 * 1000）; 
 var end = new Date（today.getTime（） -  1 * 24 * 60 * 60 * 1000）; 
 var dateString = Utilities.formatDate（start，Session.getScriptTimeZone（），'dd.MM。'）+' - '+ Utilities.formatDate（end，Session.getScriptTimeZone（），'dd.MM。'） 
 
 var nc = 2 //移动增长率的cols数量
 
函数main（）{
 copyCols（）; 
 getReports（）; 
 
 $ b函数copyCols（）{
 var ss = SpreadsheetApp.openById（'1dSpy7teczLwViKbfr-VjfQRuOq3iaRfSm3LghFldjZk'）
 var sh_DB = ss.getSheetByName（'data'） 
 var w = sh_DB.getLastColumn（）; //宽度
 var h = sh_DB.getLastRow（）; // heigth 
 //插入新列
 sh_DB.insertColumnAfter（w）; 
 //将最后n个cols拷贝到下一个col 
 for（i = 0; i <= nc; i ++）{
 sh_DB.getRange（1，wi，h，1）.copyTo sh_DB.getRange（1，W-I + 1，H，1））; 
} 
 sh_DB.getRange（1，w-nc + 1）.setValue（dateString）; 
 
 $ b $ function getReports（）{
 var sh_DB = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（'data'）
 var w = sh_DB.getLastColumn（）; //宽度
 var h = sh_DB.getLastRow（）; // heigth 
 dc = sh_DB.getRange（1，w-nc）; // lastDataColumn 
 data = [50，60，870，2]; 
 report = {'rows'：[2,3,4,5]}; 
 for（i in data）{
 sh_DB.getRange（report ['rows'] [i]，w-nc）.setValue（data [i]）; 
 
 
  

感谢您提供任何帮助。

解决方案

添加 copyCols（）和 getReports（），以告知Google Apps脚本引擎在运行第二个脚本之前应用第一个脚本所做的更改。

I am working on a kinda large sheet which I would like to update on a regular basis (weekly) via Google Apps Scripts. Every week I need to add a new column at the "end" (lastDataColumn) of my sheet and then move the last two colums (with Fomulas to calculate weekly relative changes) to the "end" (ie move them one column to the right). This leaves me with a blank column addressed with lastDataColumn - 2. This is where the new report data will go.

I have two functions. copyCols and getReports.

They both work fine on their own, so copyCols creates a new empty column at position lastDataColumn - 2 using the method explained above - and getReports fetches report Data from Analytics, third party APIs and other sheets and then writes these values in the column at position lastDataColumn - 2.

However, if I group these two functions in let's say my main function which I then want to trigger on a 7-day basis, copyCols seems to only execute to the point of creating a new empty column. Then getReports executes fully and writes all data in lastDataColumn - 2. But no columns were moved, so getReports overwrites last weeks data. After executing everything from getReports copyCols starts moving the rows (ie copying). This leaves me with a duplicate column of lastDataColumn - 3 (which should have last weeks data, but was overwritten with this weeks data because it was still in lastDataColumn - 2 before the execution of getReports) in lastDataColumn - 2.

To clarify: Executing copyCols and getReports afterwards (each on it's own) works perfectly fine.

Is Google Apps Script threadless? If so why does the problem described happen? Do "bulk" operations (like copying ranges) execute at the end of the script?

Code:

var today = new Date();
var start = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);
var end = new Date(today.getTime() - 1 * 24 * 60 * 60 * 1000);
var dateString=Utilities.formatDate(start, Session.getScriptTimeZone(),'dd.MM.')+'-'+Utilities.formatDate(end, Session.getScriptTimeZone(),'dd.MM.')

var nc=2 //num of cols with growth rates to move

function main() {
  copyCols();
  getReports();
}

function copyCols(){
  var ss=SpreadsheetApp.openById('1dSpy7teczLwViKbfr-VjfQRuOq3iaRfSm3LghFldjZk')
  var sh_DB=ss.getSheetByName('data')
  var w=sh_DB.getLastColumn(); //width
  var h = sh_DB.getLastRow();  //heigth
  // insert new column
  sh_DB.insertColumnAfter(w);
  // copy last n cols to next col
  for (i=0;i<=nc;i++){
    sh_DB.getRange(1,w-i,h,1).copyTo(sh_DB.getRange(1,w-i+1,h,1));
  }
  sh_DB.getRange(1,w-nc+1).setValue(dateString);
}

function getReports(){
  var sh_DB=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data')
  var w=sh_DB.getLastColumn(); //width
  var h = sh_DB.getLastRow();  //heigth
  dc=sh_DB.getRange(1,w-nc); //lastDataColumn
  data = [50, 60, 870, 2];
  report = {'rows':[2,3,4,5]};
  for (i in data){
    sh_DB.getRange(report['rows'][i],w-nc).setValue(data[i]);
  } 
}

Thank you for any help provided.

解决方案

Add SpreadsheetApp.flush() between copyCols() and getReports() in order to tell to the Google Apps Script engine to apply the changes made by the first before running the second.

这篇关于脚本结尾的copyToRange（以及类似的）执行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！