copyToRange(和类似的)在脚本结束时执行? [英] copyToRange (and similar) execution at end of script?

问题描述

我正在处理一个有点大的工作表，我想通过 Google Apps Scripts 定期(每周)更新它.每周我都需要在我的工作表的结束"(lastDataColumn)添加一个新列，然后将最后两列(使用公式计算每周相对变化)移动到结束"(即将它们向右移动一列)).这给我留下了一个空白列，地址为 lastDataColumn - 2.这是新报告数据所在的位置.

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.

我有两个功能.copyCols 和 getReports.

I have two functions. copyCols and getReports.

它们各自都能正常工作，因此 copyCols 在位置 lastDataColumn - 2 使用上述方法创建一个新的空列 - getReports 从 Analytics、第三方 API 和其他工作表中获取报告数据，然后将这些值写入位置 lastDataColumn - 2 处的列.

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.

但是，如果我将这两个函数分组到我的主要函数中，然后我想在 7 天的基础上触发它，那么 copyCols 似乎只执行到创建一个新的空列的程度.然后 getReports 完全执行并写入 lastDataColumn - 2 中的所有数据.但是没有列被移动，所以 getReports 覆盖了上周的数据.从 getReports copyCols 执行所有操作后，开始移动行(即复制).这给我留下了 lastDataColumn - 3 的重复列(它应该有上周的数据，但被本周的数据覆盖，因为它在执行 getReports 之前仍然在 lastDataColumn - 2 中)在 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.

澄清:事后执行 copyCols 和 getReports(每个都单独执行)效果很好.

Google Apps 脚本是无线程的吗?如果是这样，为什么会发生所描述的问题?是否在脚本末尾执行批量"操作(如复制范围)?

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?

代码:

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.

推荐答案

添加 SpreadsheetApp.flush() 位于 copyCols() 和 getReports() 之间，以便通知 Google Apps 脚本引擎应用更改在运行第二个之前由第一个制作.

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屋！