Как сделать вложенный цикл в google spreadsheet?

Question

[Ayan Bai]

У меня список покупателей и товаров.

Покупателей 30, а товаров 400.

Мне нужно чтобы у каждого покупателя были свои товары. То есть будет большое полотно, для 1 покупателя будет 400 товаров, для 2 покупателя также, и так далее для каждого покупателя. Это нужно для того чтобы я смог отдельно для каждого покупателя настроить товар.

Я имею 2 таблицы google spreadsheet. 1 - покупатели, 2 - товары. Каждая таблица может периодический обновляться.

Проблема в том что товары перезаписываются, вместо того чтобы на листе быть 12000 строк, я получаю всего 400.

Вот такой скрип я написал. Помогите с правильным скриптом. Тут я думаю нужны чисто знания JavaScript-а. Не могу выстроить правильную логику.

И еще я получаю ошибку на самом последней строке: TypeError: Cannot read property "0" from undefined.
Система указывает на эту строчку кода

sheet.getRange(i, 2).setValue(products[i][0]);

Мой код:

function writeData(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();   

  var buyers = getBuyers();
  var products= getProducts();
  
  sheet.getDataRange().clear();
  
  for (var j = 1; j <= buyers .length; j++)
  {
    var i = 0; 
    while (i <= products.length)
    {
      i++;
      sheet.getRange(i, 1).setValue(buyers [j]);
      sheet.getRange(i, 2).setValue(products[i][0]);
      sheet.getRange(i, 3).setValue(products[i][4]);
    }
  }
}

Answer 1

[Alexander Ivanov]

Ни при каких обстоятельствах не дразните систему setValue(), да еще и во вложенном цикле. Запись происходит не на ваш ПК, а на сервер. Кому понравится, когда его так дергают?

Чтобы работало даже с "черновиками", т.е. до 50 000 строк на лист, сначала берите ВСЕ данные, потом формируйте ГОТОВЫЙ массив, потом делайте setValue().

Примерно так:

function writeData(){
  //Не факт, что поможет, но вдруг
  SpreadsheetApp.flush();
  
  //Далее как у всех
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var resultSheet = ss.getSheetByName("Result");
  var agentsSheet = ss.getSheetByName("Buyers");
  var productsSheet = ss.getSheetByName("Products");
  
  var aV = agentsSheet.getDataRange().getValues();
  var pV = productsSheet.getDataRange().getValues();
  
  //срезать шапку
  aV.shift();
  //  pV.shift();
  
  var res = [];
  
  for(var i = 0; i< aV.length; i++)
  {
    res.push([].concat([aV[i][0]], pV[0]));    
    for(var j = 1; j < pV.length; j++){      
      res.push([].concat([''], pV[j]));      
    }
  }
  
  res = res.slice(0, 50000);
  resultSheet.getDataRange().clear(); 
  resultSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

Comments

[Ayan Bai]

Александр, огромное спасибо!

Первый раз у меня получилось так как не нужно было делать, после этого рабочая версия и ваша версия как нужно делать правильно. Огромное спасибо!

Сегодня же опробую.

Answer 2

[Ayan Bai]

Решил вот таким вот способом:

function writeData(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var resultSheet = ss.getSheetByName("Result");
var agentsSheet = ss.getSheetByName("Buyers ");
var productsSheet = ss.getSheetByName("Products");

var buyersNumRows = buyersSheet.getDataRange().getNumRows();
var productsNumRows = productsSheet .getDataRange().getNumRows();
var productsNumColumns = productsSheet.getDataRange().getNumColumns();

var buyersData = agentsSheet.getDataRange().getValues();
var productsData = productsSheet .getDataRange().getValues();

resultSheet.getDataRange().clear();

for(var i=1; i< buyersNumRows ; i++)
{
var lastrow = resultSheet.getLastRow();
resultSheet.getRange(lastrow+1, 1).setValue(buyersData [i][0]);
resultSheet.getRange(lastrow+1, 2, productsNumRows , productsNumColumns).setValues(products);
}
}

в итоге получилось 24 000 строк. Задача выполнена, для всех покупателей сделали свои услуги.

Answer 3

[Леонид Сысолетин]

sheet.getRange(i, 1) => sheet.getRange(i + (j * 400), 1)

Comments

[Ayan Bai]

Взрыв мозга))) как это?

[Леонид Сысолетин]

Саян Баетов: Предположим, у каждого покупателя есть по пять товаров. Для первого покупателя товары выводятся в строках 1..5, верно? Для второго - 6..10. Для третьего - 11..15. Видите закономерность?
Товар i для покупателя j будет находиться в строке i + ((j - 1) * N), где N - это число товаров.

[Леонид Сысолетин]

Саян Баетов: у вас в условии стоит число 400.

[Ayan Bai]

Леонид Сысолетин: да, все правильно. Я запарился. Ссори.

Все же я не могу понять как можно сделать.

Я написал следующим образом.

for (var j = 0; j <= buyers.length; j++)
  {
    var i = 0;
    while(i <= products.length)
    {
      sheet.getRange(i + ((j - 1) * products.length), 1).setValue(buyers[j]);
      sheet.getRange(i + ((j - 1) * products.length), 2).setValue(products[i][0]);
    }
 }

Выдает ошибку: The coordinates or dimensions of the range are invalid

[Леонид Сысолетин]

Саян Баетов: У вас покупатели считаются от нуля, так что отнимать единичку не нужно.

[Ayan Bai]

Теперь начинает печатать с строки 401 до 800 строки.

[Ayan Bai]

Заработало! Большое спасибо!
Я вам не правильно написал. Покупателей 120, а товаров 497.

Большое полотно получилось, но почему то с строки 1 до 496 пустые. Данные начинаются записываться с строки 497. Как избавится от этого?

[Ayan Bai]

Теперь мой код выглядит так:

for (var k = 1; k <= buyers.length; k++)
{
for (var i = 1; i <= products.length-1; i++)
{
sheet.getRange(i+ (k * products.length-1), 1).setValue(buyers[k]);
sheet.getRange(i+ (k * products.length-1), 2).setValue(products[i][0]);
sheet.getRange(i+ (k * products.length-1), 3).setValue(products[i][4]);
}
}

[Ayan Bai]

К сожалению, гугл скрипт выдал ошибку что время истекло время обработки сркипта. Но, я думаю, если убрать тестовых покупателей все должно работать.

[Alexander Ivanov]

Что значит запись "=>" ?

[Ayan Bai]

Имеется ввиду переписать это sheet.getRange(i, 1) на sheet.getRange(i + (j * 400), 1) :)

[Alexander Ivanov]

sbeatport: спасибо. Подумал, вдруг, пропустил какой-то важный оператор.