0
\$\begingroup\$

I have a Google Spreadsheet with a few hundreds of sheets there. Users update those sheets with new data under a strict and specific template. I created a Google Apps Script where once every while, I run it to change several things on every sheet, by keeping the original data intact and export those in multiple CSV files I store in Google Drive. In more details,

  1. Iterate on every sheet
  2. Duplicate the current one
  3. Run a set of functions on every cell I have to update (~500 on each)
  4. Export the sheet to CSV and store it in Drive
  5. Delete the temp sheet
  6. Move to the next one

Number 3 is the most time consuming. It might takes 30-40 seconds for every sheet and the functions are simple math formulas or dictionaries.

Here is the code which I have removed functions that I just repeat for more cells.

function saveAsCSV() {
  
  var maxSheetID = 100;
  var sheetsFolder = DriveApp.getFoldersByName('sheetsFolder_CSV').next();
  var folder = sheetsFolder.createFolder('sheetsFolder' + new Date().getTime());

  for (var sheetID = 1; sheetID <= maxSheetID; sheetID++) {
    createTempSheet();
    copyRowsWithCopyTo(sheetID);
    
    var tempSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('temp');
    tempSheet.getDataRange().setDataValidation(null); //remove all validations I have

    var cell1 = tempSheet.getRange(5,4);
    
    cell1.setValue(function1(cell1.getValue()));

    var cell2 = tempSheet.getRange(6,4);
    cell2.setValue(function2(cell2.getValue()));

    var cell3 = tempSheet.getRange(7,4);
    cell3.setValue(function3(cell3.getValue()));

    //continue for several cells like this on specific i,j indexes. No pattern.

    //Table
    for (var p = 9; p <= 30; p++) {
      var tCell1 = tempSheet.getRange(p,2);
      tCell1.setValue(function5(tCell1.getValue()));

      var tCell2 = tempSheet.getRange(p,3);
      tCell2.setValue(function6(tCell2.getValue()));

      //continue for several cells like this with dynamic p and several columns

    }


    fileName = sheetID + ".csv";
    var csvFile = convertRangeToCsvFile_(fileName, tempSheet);
    folder.createFile(fileName, csvFile);
    
  }
  Browser.msgBox('Files are waiting in a folder named ' + folder.getName());

}

function createTempSheet() {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var tempSheet = activeSpreadsheet.getSheetByName("temp");

  if (tempSheet != null) {
    activeSpreadsheet.deleteSheet(tempSheet);
  }

  tempSheet = activeSpreadsheet.insertSheet();
  tempSheet.setName("temp");

}

function convertRangeToCsvFile_(csvFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var csvFile = undefined;

    // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var 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) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}


function copyRowsWithCopyTo(sourceName) {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName(sourceName);
  
  let sourceRange = sourceSheet.getDataRange();
  
  let targetSheet = spreadSheet.getSheetByName('temp');
  
  sourceRange.copyTo(targetSheet.getRange(1, 1));
}
\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.