Return to Revisions

As the documentation says,

Using JavaScript operations within your script is considerably faster than calling other services. Anything you can accomplish within Google Apps Script itself will be much faster than making calls that need to fetch data from Google's servers or an external server, such as requests to Spreadsheets, Docs, Sites, Translate, UrlFetch, and so on. Your scripts will run faster if you can find ways to minimize the calls the scripts make to those services.

The slowest part in the script is all those .getValue() and .setValue() calls. Not only is the script making those calls, it is alternating them as well, even in the same line(rg.setValue(function(rg.getValue()))). Alternating read and write is slow:

Every time you do a read, we must first empty (commit) the write cache to ensure that you're reading the latest data (you can force a write of the cache by calling SpreadsheetApp.flush()). Likewise, every time you do a write, we have to throw away the read cache because it's no longer valid. Therefore if you can avoid interleaving reads and writes, you'll get full benefit of the cache.

• http://googleappsscript.blogspot.com/2010/06/optimizing-spreadsheet-operations.html

The script here leaves out essential information. What does function1 do? More importantly, whether function1 does anything to the data. Does it modify the sheet again? Also, when getting the csv, does the sheet make any calculations through formula?

Ideally, Your script structure should look like:

• INPUT: Get data from one sheet
• OUTPUT: Set modified data to Drive

This implies:

• no temporary sheet creation,
• no repeated get/set calls,
• no relying on sheet formulas after getting data

Other minor changes include:

• Use const and let as needed instead of declaring all variables as var. This helps the javascript engine optimize the memory and processing of such variables

• The script repeats almost all the code multiple times. Practice DRY(Don't Repeat Yourself) principle. See eg below. Use loops where possible: Even in cases, where there isn't a pattern, you can create a list of indexes that you want and then loop over them.

• insertSheet is able to create new sheet with old sheet as a template. So, the functions createTempSheet and copyRowsWithCopyTo are completely unnecessary.

• console is a newer standard class. Use it instead of Logger

Script with sample modifications:

function saveAsCSV() {
  const maxSheetID = 100;
  const sheetsFolder = DriveApp.getFoldersByName('sheetsFolder_CSV').next();
  const folderName = 'sheetsFolder' + new Date().getTime();
  const folder = sheetsFolder.createFolder(folderName);
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  for (let sheetID = 1; sheetID <= maxSheetID; sheetID++) {
    /*needed? why not directly geet the data*/ const tempSheet = ss.insertSheet(
        'temp',
        1,
        {
          template: ss.getSheetByName(sheetID),
        }
      ),
      datarange = tempSheet.getDataRange(),
      data = datarange.getValues(),
      indexes = [
        [5, 4], // automatically use  index to  calculate  function number
        [6, 4],
        [7, 4, 'function3'], // or specify a function
      ];
    datarange.setDataValidation(null); //remove all validations I have
    indexes.forEach(/*DRY loop*/
      ([i, j, func], funcIdx) =>
        (data[i][j] = func ? func(data[i][j]) : this[`function${funcIdx + 1}`])
    );
    //continue for several cells like this on specific i,j indexes. No pattern.
    //Table
    for (let p = 9; p <= 30; p++) {
      data[p][2] = function5(data[p][2]);
      data[p][3] = function5(data[p][3]);
      //continue for several cells like this with dynamic p and several columns
    }
    /*needed?*/ datarange.setValues(data);
    const fileName = sheetID + '.csv';
    const csvFile = convertRangeToCsvFile_(/*DRY*/data);
    folder.createFile(fileName, csvFile);
    ss.deleteSheet(tempSheet);
  }
  Browser.msgBox('Files are waiting in a folder named ' + folderName/*DRY*/);
}


function convertRangeToCsvFile_(data) {
  try {
    let csvFile;
    // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
      let csv = '';
      for (let row = 0; row < data.length; row++) {
        for (let col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(',') != -1) {
            data[row][col] = '"' + data[row][col] + '"';
          }
        }
        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (row < data.length - 1) {
          csv += data[row].join(',') + '\r\n';
        } else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  } catch (err) {
    console.log(err);
    Browser.msgBox(err);
  }
}