如何在javascript循环中创建批处理以选择x行，然后选择下x行，直到完成所有行? [英] how can I create batch in javascript loop to select x Rows then next x Rows until all rows are done?
问题描述
我有一个带有一些数据的google工作表，我正在尝试将所有单元格数据合并到一个JSON变量中，以便将其传递给API来做某事.
I have a google sheet with some data and I am trying to combine all cell data in a JSON variable so I can pass it on to API to do something.
我有这个javascript函数，它可以接收所有数据，并像这样将所有内容组合到JSON变量中:
I have this javascript function that takes all data and combine everything in JSON variable like this:
function combine_val() {
var startRow = 2; // First row of data to process. Starting with 2 to ignore headers
var startColumn = 1; //First Column to process, in case that changes.
var numRows = mysheet.getLastRow(); // Number of rows to process
var numCols = mysheet.getLastColumn(); //Also the number of columns to process, again in case that changes.
var dataRange = mysheet.getRange(startRow, startColumn, numRows, numCols);//Get the full range of data in the sheet dynamically.
var data = JSON.stringify(dataRange.getValues());//Get the value of the range, AND convert it to a JSON string in one line.
// DO something HERE with "data" to push the JSON string in a controlled batch to API
SpreadsheetApp.getUi().alert(data);
}
我传递此数据的API仅使用200行的JSON.因此，在创建200个批次时，我需要帮助.
The API where I am passing this data takes JSON with 200 rows only. So I need help in creating a batch of 200.
这是我到目前为止要做的，需要帮助.
This is what I have done so far and need help.
var mybatch = 200;
function combine_val_increment() {
var startRow = 2; // First row of data to process. Starting with 2 to ignore headers
var startColumn = 1; //First Column to process, in case that changes.
var numRows = mysheet.getLastRow(); // Number of rows to process
var numCols = mysheet.getLastColumn(); //Also the number of columns to process, again in case that changes.
for (var i = 0; i < numRows/mybatch; ++i) {
var dataRange = mysheet.getRange(startRow, startColumn, startRow+mybatch, numCols);//Get the full range of data in the sheet dynamically.
var data = JSON.stringify(dataRange.getValues());//Get the value of the range, AND convert it to a JSON string in one line.
// DO something HERE with "data" to push the JSON string in a controlled batch to API
SpreadsheetApp.getUi().alert(data);
startRow = startRow + mybatch;
}
}
基于建议/评论的方法#2
function rowsForAPI2(){
var batchsize = 2;
//var batchsize = 200;
//var ss = SpreadsheetApp.getActiveSheet();
var ss = SpreadsheetApp.getActive().getSheetByName('Sheet5'); //SHEET NAME
// var data = ss.getDataRange().getValues(); // 2D array with all of the data in the sheet.
var startRow = 2; // First row of data to process. Skip 1st row of column headers for this test.
var startColumn = 1; //First Column to process, in case that changes.
var numRows = ss.getLastRow(); // Number of rows to process
//var numCols = mysheet.getLastColumn(); //Also the number of columns to process, again in case that changes.
var numCols = 4; //Hardcode for this test
var dataRange = ss.getRange(startRow, startColumn, numRows, numCols);//Get the full range of data in the sheet dynamically.
var data = dataRange.getValues();//Get the value of the range, AND convert it to a JSON string in one line.
var rowCount = ss.getLastRow() - 1; // To know how many rows have data (-1 will ignore the column header)
var obj = [];
var temp = 0;
var results = [];
Logger.log(rowCount/batchsize)
for (var i = 0; i < (rowCount/batchsize); i++){
for (var j = temp; j < batchsize*(i+1); j++){
obj.push(data[j]); // Push row into object.
temp = j;
if (temp == rowCount-1) // Got to the end of the data.
break;
}
temp++;
results.push(JSON.stringify(obj)); // Adds the JSON object to an array
obj = []; // Clear the array of the 200 rows stored
}
return results;
}
function doSomething(){
var objects = rowsForAPI2();
var curr;
for ( var i = 0; i < objects.length; i++){
curr = objects[i];
// Do the API thing with curr...
Logger.log(curr);
}
}
方法3的新要求-
在此新用例中，而不是在200行批处理的JSON.stringify数组中传递数据.我有一个API端点，它采用这种格式的行:
In this new use-case, instead of passing data in JSON.stringify array of 200 rows batch. I have an API endpoint that takes rows in this format:
{
"recipient": {
"emailAddress": "email_1@domain.com",
"listName": {
"path": "testfolder"
}
}
},
{
"recipient": {
"emailAddress": "email_2@domain.com",
"listName": {
"path": "testfolder"
}
}
},
{
"recipient": {
"emailAddress": "email_3@domain.com",
"listName": {
"path": "testfolder"
}
}
}
我如何使用下面讨论的与批处理技术相同的解决方案，但如何构建以上^格式的记录，其中电子邮件列表来自Google工作表中的行值?有帮助吗?
How can I use same solution discussed below with batching technique but for building the above^ formatted records where email list is coming from values in rows in google sheet? Any help?
推荐答案
尝试一下:
function rowsForAPI(){
var ss = SpreadsheetApp.getActiveSheet();
var data = ss.getDataRange().getValues(); // 2D array with all of the data in the sheet.
var rowCount = ss.getLastRow(); // To know how many rows have data
var obj = []; // Array where the row objects will be stored
var temp = 0; // A counter of how many rows have been processed.
var results = []; // Array where the resulting JSON objects will be stored and returned.
Logger.log(rowCount/200)
for (var i = 0; i < (rowCount/200); i++){
for (var j = temp; j < 200*(i+1); j++){
obj.push(data[j]); // Push row into object.
temp = j;
if (temp == rowCount-1) // Got to the end of the data (if there are less than 200 rows in this batch).
break;
}
temp++; // Update row count.
results.push(JSON.stringify(obj)); // Adds the JSON object to an array
obj = []; // Clear the array of the 200 rows stored before the next loop starts.
}
return results;
}
function doSomething(){
var objects = rowsForAPI();
var curr;
for ( var i = 0; i < objects.length; i++){ // Go through each batch
curr = objects[i]; // Current batch.
// Do the API thing with curr...
}
}
此方法将返回一个JSON对象数组，该数组保存来自Sheet的200行的批处理，如果到达工作表中数据的末尾，它也会停止.
This method will return an array of JSON objects that holds batches of 200 rows from the Sheet, it will also stop if it reaches the end of the data in the sheet.
这篇关于如何在javascript循环中创建批处理以选择x行，然后选择下x行，直到完成所有行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
