Блог

В настоящее время я работаю над проектом, который перекрестно проверяет 2 листа примерно с 500 циклами.

СПИСОК

REGISTRATION

Результат

Пример таблицы данных моя проблема в том, что мой скрипт иногда получает время ожидания во время выполнения. Я вижу функцию map, но я не уверен, применима ли она для моей текущей проблемы.

вот мои листы, которые включены в мой скрипт. листы — это реестр, ответы формы 1, ссылка, результат. реестр и вкладка регистрация имеют общие столбцы (имя, фамилия, дата рождения)

 var SS              = SpreadsheetApp.getActiveSpreadsheet()
var rosterTab       = SS.getSheetByName('Roster')
var registrationTab = SS.getSheetByName('Responses 1')
var referenceTab    = SS.getSheetByName('Reference')
var resultTab       = SS.getSheetByName('Result') //this is where I posts all registered names that is on the roster tab.
var xLastRow        = registrationTab.getLastRow();
var yLastRow        = rosterTab.getLastRow();
var Data            = [];
var codeStart       = referenceTab.getRange('A1').getValue();
var rosterdata      = rosterTab.getRange(1,1,300,3).getValues();
var registerdata    = registrationTab.getRange(1,1,xLastRow,3).getValues();

for(var i = 0; i<yLastRow; i  (){
    for(var j = 0; j<xLastRow; j  (){
        if (rosterdata[i][0] === registerdata[j][0] amp;amp; rosterdata[i][1] === registerdata[j][1] amp;amp; rosterdata[i][2] === registerdata[j][2]){
            var rosterFname = registerdata[j][0];
            var rosterLname = registerdata[j][1];
            var rosterDoB   = registerdata[j][2];
            var rosterCode  = codeStart;

            Data.push([rosterFname,rosterLname,rosterDoB,rosterCode]);

            break;
        }
    }
}
resultTab.getRange( resultTab.getLastRow() 1, 1, Data.length,Data[0].length ).setValues(Data);
 

Есть ли другой способ приблизиться к этому сопоставлению без тайм-аута?

 function myfunky11() {
  const ss = SpreadsheetApp.getActive();
  const rosterTab = ss.getSheetByName('Roster');
  const registrationTab = ss.getSheetByName('Responses 1');
  const referenceTab = ss.getSheetByName('Reference');
  let rsh = ss.getSheetByName('Result'); 
  var data = [];
  const codeStart = referenceTab.getRange('A1').getValue();
  const ros = rosterTab.getRange(2, 1, rosterTab.getLastRow() - 1, 3).getDisplayValues().map(r => r[0]   r[1]   r[2]);
  const reg = registrationTab.getRange(2, 1, registrationTab.getLastRow() - 1, 3).getValues();
  reg.forEach(r => {
    let s = r[0].toString()   r[1].toString()   Utilities.formatDate(new Date(r[2]), Session.getScriptTimeZone(), "M/d/yyyy");
    if (ros.indexOf(s)>-1) {
      r.push(codeStart);
      data.push(r)
    }
  });
  rsh.clear();
  rsh.getRange(1, 1, data.length, data[0].length).setValues(data);
}
 

Теперь это работает

Вы можете обрабатывать все данные «Реестра» пакетами, отслеживая последнюю строку, которую вы обработали с помощью PropertiesService

 const LAST_PROCESSED_ROW_PROPERTY_KEY = 'lastProcessedRow';
const PROCESS_ROW_PER_BATCH           = 100;

var scriptProperties = PropertiesService.getScriptProperties();
var lastProcessedRow = scriptProperties.getProperty( LAST_PROCESSED_ROW_PROPERTY_KEY );

if ( lastProcessedRow === null ) {
    lastProcessedRow = 0;
}

// Your variables here

var yLastRow          = rosterTab.getLastRow();
var rowsToBeProcessed = PROCESS_ROW_PER_BATCH;

if ( lastProcessedRow == yLastRow ) {
    Logger.log( 'No new data to be processed' );

    return;
}

if ( yLastRow - lastProcessedRow < PROCESS_ROW_PER_BATCH ) {
    rowsToBeProcessed = yLastRow - lastProcessedRow;
}

var rosterdata = rosterTab.getRange( lastProcessedRow   1, 1, rowsToBeProcessed, 3 ).getValues();

for( var i = 0; i < rosterdata.length; i   ) {
    // Your data processing logic here
}

// Set data logic here

scriptProperties.setProperty( LAST_PROCESSED_ROW_PROPERTY_KEY, lastProcessedRow   rowsToBeProcessed );
 

Я считаю, что ваша цель заключается в следующем.

• В вашей электронной таблице 3 листа.
• Вы хотите получить значения из таблиц «Форма ответа 1» и «Реестр». Когда значения из столбцов «B» в «D» на листе «Форма ответа 1» совпадают со значениями столбцов «A» в «C» на листе «Реестр», вы хотите поместить их в лист «Результат» в качестве добавляемых значений.
• Вы хотите снизить стоимость этого процесса.

В этом случае, как насчет следующего модифицированного сценария?

Модифицированный скрипт:

 function myFunction() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var rosterTab = SS.getSheetByName('Roster');
  var registrationTab = SS.getSheetByName('Form Response 1');
  var referenceTab = SS.getSheetByName('Reference');
  var resultTab = SS.getSheetByName('Result');
  var xLastRow = registrationTab.getLastRow();
  var yLastRow = rosterTab.getLastRow();
  var Data = [];
  var codeStart = referenceTab.getRange('A1').getValue();
  var rosterdata = rosterTab.getRange(1, 1, yLastRow, 3).getValues();
  var registerdata = registrationTab.getRange(2, 2, xLastRow - 1, 3).getValues();

  // I modified below script.
  // Create an object.
  var obj = rosterdata.reduce((o, [a,b,c]) => (o[a   b   c] = true, o), {});

  // Create an array for putting to sheet.
  var values = registerdata.reduce((ar, [a, b, c]) => {
    if (obj[a   b   c]) ar.push([a, b, c, codeStart]);
    return ar;
  }, []);
  
  // Put the array to the result sheet.
  if (values.length == 0) return;
  resultTab.getRange(resultTab.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}
 

• В этой модификации создайте объект для поиска значений и создайте массив для добавления на лист, а созданный массив будет добавлен к таблице результатов. С помощью этого потока я подумал, что стоимость процесса может быть немного снижена.

Ссылка:

• уменьшить ()