0

I have a code working fine but not optimized (I am new to Google App script).

This code is doing the following :

  • Get data from external URL
  • Filter the data
  • Parse data in sheets contained in a folder
  • Change columns header
  • Appen content in a specific column
function myfunction() {
  var keywords = ["valuetoremove1", "valuetoremove2"]; //  filter the column "C".

  // Retrieve CSV data.
  var csvUrl = "https://myurl";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent, ";");

  // Retrieve Spreadsheet and put the CSV data.
  var root = DriveApp.getFoldersByName("Folder1");
  while (root.hasNext()) {
    var folder = root.next();
    var files = folder.getFiles();
    while (files.hasNext()) {
      var spreadsheet = SpreadsheetApp.open(files.next());
      var name = spreadsheet.getName().toUpperCase();
      var values = csvData.reduce((ar, r) => {
        if (!keywords.some(e => r[2].toUpperCase().includes(e.toUpperCase())) && r.join("").toUpperCase().includes(name)) {
          ar.push(r);
        }
        return ar;
      }, []);
      if (values.length == 0) continue;
      var sheet = spreadsheet.getSheets()[0];
      sheet.clearContents().getRange(2, 1, values.length, values[0].length).setValues(values);

// modify column titles
var cell = sheet.getRange(1,1);
cell.setValue("Column1");
var cell = sheet.getRange(1,2);
cell.setValue("Column2");
var cell = sheet.getRange(1,3);
cell.setValue("Column3");
var cell = sheet.getRange(1,4);
cell.setValue("Column4");
var cell = sheet.getRange(1,5);
cell.setValue("Column5");

// Modify column E
var dataRange = spreadsheet.getDataRange().getValues();
  var colData = [];

  for (var i = 1; i < dataRange.length; i++) {
    colData.push(dataRange[i][0]);
  }
  for (var i = 0; i < colData.length; i++) {

    // Get column E
    var comments_cell = spreadsheet.getDataRange().getCell(i + 2, 5).getValue();

        // Append
          spreadsheet.getDataRange().getCell(i + 2, 5).setValue('<a href="' + comments_cell + '" target="_blank"><button type="button">Button</button></a>');
    }

    }
  }
}

It works but it takes ages, especially the last part, the lines are being changed one by one. Is there any way to make it much faster ?

Thanks

Antoine
  • 63
  • 6

1 Answers1

0

Try (after // Modify column E)

  var dataRange = spreadsheet.getDataRange().getValues()

  for (var i = 1; i < dataRange.length; i++) {
    // Get column E
    var comments_cell = dataRange[i][4];
    dataRange[i][4] = '<a href="' + comments_cell + '" target="_blank"><button type="button">Button</button></a>'
  }

  spreadsheet.getDataRange().setValues(dataRange)
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20