for...in 循环的执行速度非常慢

问题描述

我正在根据从外部端点获取的数据填充电子表格.一切正常，但 for...in 循环非常慢.

I am populating a spreadhsheet from data I am getting from an external endpoint. Everything is working, but the execution of the for...in loop is incredibly slow.

我从 UrlFetchApp 返回的对象是相当大；Object.keys().length >1500，所以也许它可能只是对象的大小.

The object that I am getting back from UrlFetchApp is quite large; Object.keys().length > 1500, so perhaps it could just be the size of the object.

我很好奇有没有办法优化我的功能.

I am curious if there is a way to optimize my function.

代码:

    var sh = SpreadsheetApp.getActiveSheet();
    function getData() {
      var response = UrlFetchApp.fetch(endpoint);
      var data = JSON.parse(response);
      var rates = data["quotes"];
      var max = Object.keys(rates).length;
      var row = 2;
      var i = 0;
      clear(); //clears the range A2:B
      for (var key in rates) {
        sh.getRange("A" + (i+row)).setValue(key.substring(3));
        sh.getRange("B" + (i+row)).setValue(rates[key]);
        i++
        if (i+1 === max) {
          //do something after complete
        }
      }
    }

推荐答案

我不确定 //在完成后做某事.那么这两种模式怎么样.请将此答案视为多个答案之一.

I was not sure about //do something after complete. So how about these 2 patterns. Please think of this answer as one of several answers.

我修改了您的脚本以将 setValue() 替换为 setValues().这样，处理速度会很快.

I modified your script to replace setValue() to setValues(). By this, the process speed will be fast.

function getData1() {
  var sh = SpreadsheetApp.getActiveSheet();
  var response = UrlFetchApp.fetch(endpoint);
  var data = JSON.parse(response);
  var rates = data["quotes"];

  var keys = Object.keys(rates);
  var dat = [];
  keys.forEach(function(key){
    if (key != keys[keys.length - 1]) {
      dat.push([key.substring(3), rates[key]]);
    } else {
      //do something after complete
    }
  });
  sh.getRange(2, 1, dat.length, dat[0].length).setValues(dat);
}

模式 2:

function getData2() {
  var sh = SpreadsheetApp.getActiveSheet();
  var response = UrlFetchApp.fetch(endpoint);
  var data = JSON.parse(response);
  var rates = data["quotes"];

  var dat = Object.keys(rates).map(function(key){return [key.substring(3), rates[key]]});
  sh.getRange(2, 1, dat.length, dat[0].length).setValues(dat);

  //do something after complete
}

注意:

• 如果这不起作用，您能否提供来自 var response = UrlFetchApp.fetch(endpoint); 的示例数据?当然，请删除其中的私人信息.

Note :

• If this didn't work, can you provide a sample data from var response = UrlFetchApp.fetch(endpoint);? Of course, please remove the private information from it.
• setValue(value)
• setValues(values)
• 最佳做法

如果我误解了你的问题，我很抱歉.

If I misunderstand your question, I'm sorry.

这篇关于for...in 循环的执行速度非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！