Script is running sooooo slow..

[Paul]

Hey guys! 

Could you please help me with understanding why is my script is working so slow. 

I have goole doc and wrote the script to export all the tabs automatically. It creates a folder on google disc and put there respective files. However the script working very very slow. Sometimes it refuses to continue working because of some timeout for running. 

The script is in attachment. Thank in advance!

[Ed Sambuco]

Your convertToTSV()function is way overdone.

The variable you call data is an array that already has all the row and column information you need. Just iterate through that array with a simple for loop, cut out all the cell processing, and things should move much faster.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e9896a46-de77-4512-92e8-dcac83969df2n%40googlegroups.com.

[cbmserv...@gmail.com]

Ed is correct. That function is where you need to improve speed. It seems to grab all data in sheet once and place it in Data, then it starts poking the same sheet one cell at a time inside your row/col for loops.

 

Here is what I propose you do:

• Updated your definition of var ss as you only need to grab activespreadsheet, no need to get its id and then grab it. (small efficiency here)
• Remove you for loop over Data (for (d in data), it is repeating the same thing as the other two loops
• In altData, removed the call to spreadsheet to grab cell value. You already got all the values in array data. No need to repoke the whole spreadsheet one cell at a time.

Changes in function are below. You will see where I commented out some of your statements just so you can follow the changes needed. Feel free to delete these statements once you understand what it is doing.

 

I do not know if function works or not, was just looking at efficiency.

 

function convertToTSV() 

{

  var ss = SpreadsheetApp.getActiveSpreadsheet()

  var sheets = ss.getSheets();

  var folderId = GetFolderId(ss.getName());

  var folder = DriveApp.getFolderById(folderId);

  for (var s in sheets) 

  {

    var tsv = "";

    var data = sheets[s].getDataRange().getValues();

    var sheet = sheets[s];

    var cols = sheet.getLastColumn() + 1;

    var rows = sheet.getLastRow() + 1;    

 

    Logger.log('ss:' + sheets[s].getName());

    //for (d in data) {

      var cumulative = "";

 

      for (var row = 1; row < rows; row++) {

        for (var col = 1; col < cols; col++) {

          //var altData = sheet.getRange(row, col);

          var cellDataValues = data[row-1][col-1];  //altData.getValues();

          var cellCumulative = "";

          for (var curAltValue in cellDataValues) {

            cellCumulative += cellDataValues[curAltValue];

            if (curAltValue != cellDataValues.length - 1) {

              cellCumulative += ' ';

            }

          }

 

          while (cellCumulative.indexOf('\n') > -1) {

            cellCumulative = cellCumulative.replace('\n', ' ');

          }

 

          cumulative += cellCumulative;

          if (col != cols - 1) {

            cumulative += '\t';

          }

        }

        cumulative += '\n';

        //Logger.log('cumulative:'+cumulative);

      }

      tsv = cumulative;

    //}

    folder.createFile(sheets[s].getName() + ".tsv", tsv);

  }

 

  Browser.msgBox('Files are waiting in a folder named ' + folder.getName());

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAANZsGMMbiNR%2BfgwH%2Bx%3DcyNMVkzBq8F7VgfT76MyNJn3rH4f-g%40mail.gmail.com.

[Paul]

Thank you so much guys!!! I'll take a look today! 

пятница, 2 сентября 2022 г. в 21:20:40 UTC+3, George: