Google Apps脚本查找行的最快方法? [英] Google Apps Script Fastest way to find a row?

问题描述

我试图找出最快的方法是从一个值与我的查询匹配的行中获取一些数据，我目前正在使用以下代码:

I am trying to find out what the fastest way is to get some data from a row where a value matches my query, I am currently using this code:

var spr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CustomerList'); 
  var klantNummer = array1[0].toString();
  var emailgebruiker = Session.getActiveUser().getEmail();      
  var data = spr.getDataRange().getValues(); 
  for(n=0;n<data.length;++n){ 
    if(data[n][0].toString() == klantNummer){ //.match(klantNummer)
      data[n][21] = emailgebruiker

   var KlantNR = data[n][0];
   var Email = data[n][3];
   var Stad = data[n][12];          
   var Taalschool = firstToUpperCase( data[n][13].toString() );    
   var Cursus = firstToUpperCase( data[n][14].toString() );      
   var Weken = data[n][15];
   var Accommodatie1 = data[n][17];
   var TypeAccommodatie = data[n][18];
   var TypeKamer = data[n][19];
   var VertrekDatum1 = data[n][20];

    };
  }
  spr.getRange(1,1,data.length,data[0].length).setValues(data); 

当我在小纸上使用它时，它工作正常，但是当纸变大时，它开始变慢，有更好的方法吗?

It works fine when I use it in a small sheet but when the sheet gets bigger it starts to become slow, is there a better way to do this?

推荐答案

我发现保存为Drive中文本文​​件的JSON对象的工作速度非常快，读取6 mb的文本文件然后在200毫秒内将其解析.这样，您可以将klantNummer的所有行保存到对象并调用它，获取这些行并获取所有值，在1秒内，如下所示:

I've found that JSON object saved as a text file inside Drive works really fast, a 6mb text file is read then parsed in under 200 milliseconds. With this, you can save all lines of the klantNummer to the Object and call it, get the lines and get all values, under 1 sec, as such:

保存，更新并获取klantNummer示例:

Save, update and get klantNummer examples:

function saveFile(){
  var klanNumbers = {'[e-mail] ': 3, '[e-mail]' : 12, '[e-mail]' : 33};
  return DriveApp.createFile('klanNumbers', JSON.stringify(klanNumbers));
}

function updateFile( newKlanNumb, klanLine ){
  var klanNumbFile = DriveApp.getFilesByName('klanNumbers').next(),
      file = JSON.parse(file.getAs("application/none").getDataAsString());

  if( file[ newKlanNumb ] )
    file[ newKlanNumb ] = file[ newKlanNumb ] + ',' + klanLine;
  else
    file[ newKlanNumb ] = klanLine;

  klanNumbFile.setTrashed(true);

  return DriveApp.createFile('klanNumbers', JSON.stringify( newKlanNumb ));
}

function getKlanNumbers( klanNumb ){
  var file = DriveApp.getFilesByName('Transactions').next(),
      info = file.getAs("application/none").getDataAsString(),
      klanNumbers = JSON.parse(info);

  return klanNumbers[klanNumb];
}

这篇关于Google Apps脚本查找行的最快方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！