Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.Learn more

I wrote a very slow for loop in my Google Sheets file. There are 18 sheets for which I have to copy every cell that is greater than 0 to another sheet.

My code works for my task, but it\'s very slow. Perhaps someone has an idea on how to increase its speed?

Some explanation:

I used if statement if(i == 2) because one sheet has 2700 rowsthe line if(total.getRange(16+i, 5).getValue() 1) checks if sheet have cells greater than zero. It prevents not testing empty sheets. if (total.getRange(16+i, 5).getValue() 1) { var sheet = SpreadsheetApp.setActiveSheet(formularz.getSheets()[i]); if (sheet.getRange(j, 8).getValue() 0) { var KOD = sheet.getRange(j, 3).getValue(); var il = sheet.getRange(j, 8).getValue(); wysylka.appendRow([KOD,il]); The only way getValues will be slower is if you call it every step of your loop. If you do that, then you are not understanding what getValues is used for, and did not properly implement it. Show the code you used when you were working with getValues. tehhowch Jun 18 \'18 at 0:10

You need to minimize calls to Apps Script APIs, especially within loops. Even more so within nested loops.

This is covered in the Using batch operations section of the Best Practices documentation.

Here\'s an annotated replacement that minimizes the Apps Script API calls:

NOTE: I attempted to recreate your spreadsheet since you didn\'t provide a minimal and complete example. If you want to try using this, please test with a copy of your data.

function replacement() { // For testing purposes, I created a spreadsheet that has data where// your original code expects it. I\'m not sure if this is actually all// in one spreadsheet or maybe spread across multiple, since you didn\'t // specify. var formularz = SpreadsheetApp.getActiveSpreadsheet(); var total = formularz.getSheetByName(\'total\'); var wysylka = formularz.getSheetByName(\'wysylka\'); // CONFIGURATION // These are constants to capture the script configuration that can be // easily tweaked without diving into the code. // 0-based index of the first data sheet. var START_SHEET = 1; // 0-based index of the last data sheet. // NOTE: I only populated 3 data sheets in my test environment. var END_SHEET = 3;// 0-based index of the row with sheet data within the total sheet. var TOTAL_STARTING_ROW = 16; // 0-based index of the column with sheet data within the total sheet. var TOTAL_COLUMN = 4; // 0-based index of the starting row within data sheets. var DATA_STARTING_ROW = 7; // 0-based index of the \'KOD\' column within data sheets. var DATA_KOD_COLUMN = 2; // 0-based index of the \'il\' column within data sheets. var DATA_IL_COLUMN = 7; // END OF CONFIGURATION // A prerequisite calculation based on the configuration. var numSheets = END_SHEET - START_SHEET + 1; // Use a single getValues() call to fetch all necessary total data. totalData = total.getRange(TOTAL_STARTING_ROW + 1, TOTAL_COLUMN + 1, numSheets).getValues(); // Use an array to capture all of the rows we will append to the \'wysylka\' sheet. var wysylkaRows = []; for (var i = 0; i numSheets; i++) {// Use a single getValues() call to fetch all necessary data from the data sheet. // Using getDataRange() to get all sheet data, instead of manually keeping track // of number of rows per sheet. var sheetData = formularz.getSheets()[START_SHEET + i].getDataRange().getValues(); for (var j = DATA_STARTING_ROW; j sheetData.length; j++) { if (totalData[i][0] 1) { if (sheetData[j][DATA_IL_COLUMN] 0) { // Push an array that represents a row we\'ll add to the \'wysylka\' sheet. wysylkaRows.push([ sheetData[j][DATA_KOD_COLUMN], sheetData[j][DATA_IL_COLUMN] ]);// Get a range for all of the accumulated rows. appendRange = wysylka.getRange(wysylka.getLastRow() + 1, 1, wysylkaRows.length, wysylkaRows[0].length); // Set all rows with a single setValues() call. appendRange.setValues(wysylkaRows);

As noted, in my testing I only populated 3 data sheets (instead of 17). I was able to process ~1000 rows of data in a 1.034 seconds.

OP didn t ask a good enough question to get a nearly turn-key solution (you even reference this fact in your answer). My analysis is that OP doesn t know how to work with JavaScript arrays. A simple example of using getValues and getDataRange would help OP learn more. It is good that you included links to reference documentation though. tehhowch Jun 18 \'18 at 11:56 Point taken. My intention with the comments throughout the code was to provide pointers on the various optimizations. Will rethink my approach in future answers. Thanks! chuckx Jun 18 \'18 at 18:23site design / logo 2020 Stack Exchange Inc; user contributions licensed under cc by-sa. rev2020.5.18.36843 Stack Overflow works best with JavaScript enabled

>>> 更多资讯详情请访问蚂蚁淘商城