Range#sort无法根据新公式的值进行排序 [英] Range#sort fails to sort based on new formula's values

问题描述

我正在使用Google Apps脚本将多个Google表格中的数据提取到一个主表格中.要将日期转换为星期数，我将工作表函数 ISOWEEKNUM()用于第1列.我想按周数排序，并且由于标题，我使用 Range#sort .

I am extracting data from several Google Sheets into one main sheet using Google Apps Script. To convert dates to week-numbers I use the sheet function ISOWEEKNUM() for column 1. I want to sort by week-numbers and because of headers I use Range#sort.

我遇到的问题是，当我测试运行 getData() 时没有执行排序， Range#sort 调用没有影响.如果我先运行 getData() ，然后手动运行我的 sortRange() 函数，它将起作用.

The problem I am having is that no sorting is performed when I test run getData(), the Range#sort call has no effect. If I first run getData() and then manually run my sortRange() function it works.

是否有关于编写其中包含 ISOWEEKNUM()的单元格，然后尝试在同一脚本执行中直接排序的内容?无需用户手动排序或启动更多脚本就可以解决该问题?

Is there something about writing cells with ISOWEEKNUM() in them and then try to sort directly in the same script execution? What can be done solve this without the user having to sort or start more scripts manually?

function getData()
{
  var thisSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = thisSpreadSheet.getSheetByName('Data');
  var deliverySheets = listDeliverySheets();

  var outputWeekAndTotal = [];
  var outputCratesPerStore = [];
  var i;
  for(i = 0; i < deliverySheets.length; i++)
  {
    outputWeekAndTotal.push(["=ISOWEEKNUM(\""+deliverySheets[i].getRange("A2").getDisplayValue()+"\")", deliverySheets[i].getRange("L12").getValue()]);
    outputCratesPerStore.push(deliverySheets[i].getRange("L5:L9").getValues());    
  }

  dataSheet.getRange(2, 1, outputWeekAndTotal.length, outputWeekAndTotal[0].length)
    .setValues(outputWeekAndTotal);
  dataSheet.getRange(2, 3, outputCratesPerStore.length, outputCratesPerStore[0].length)
    .setValues(outputCratesPerStore);
  sortRange();
}

function sortRange()
{
  var thisSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var rangeToSort = thisSpreadSheet.getSheetByName('Data').getRange(2, 1, 7, 7); /*Constants used temporarily*/
  rangeToSort.sort({column: 1, ascending: true});
}

推荐答案

基本问题是

The fundamental issue is that Google is free to (and does) optimize their interpretation of your code to avoid abuse of their servers. When you call functions that operate on different objects, Google doesn't always determine that the order is important, and thus may invoke certain API operations out of (your desired) order. You can help Google by chaining methods on the exact same object, but this is not always sufficient. Operations that cause side effects / asynchronous changes, such as writing a formula, or operate over different APIs - such as calling the Drive API/Service after using Form, Docs, or Sheets Service methods - may not be performed in order even if "chained."

要解决此问题，必须强制刷新写入缓存缓冲区.对于电子表格服务，可通过调用 SpreadsheetApp#flush .

To fix this, you must forcibly flush the write cache buffer. For the Spreadsheet Service, this is done via a call to SpreadsheetApp#flush.

  ...
  dataSheet.getRange(...).setValues(...);
  SpreadsheetApp.flush();
  sortRange();
}

刷新写缓存将强制在执行下一个脚本行之前计算写公式，使它们的值可用于排序方法.

Flushing the write cache will force the written formulas to be calculated prior to executing the next script lines, making their values available to the sorting method.

这篇关于Range#sort无法根据新公式的值进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！