Modify each row in imported data with a script in Google sheet-CodePudding

I have a piece of code I use to import data to several sheets, and it is working fine. But I am trying to also modify all data from a specific column to turn it into an HTML button. The goal is to turn www.mylink.com to Button

Here is the code I use to import data (fetch data, filter it and put it in sheets into a folder)

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);

I have tried a code to change the link cells in a single page and it works, but not combined with my 1st code, it crashes because there are too much data. Here is what I tried :

// 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>');
    }

Here is my complete revised script :

function myfunction() {
  var keywords = ["removedata1", "removedata2"]; //  filter the column "C".
  var csvUrl = "https://myurl";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent, ";");
  var root = DriveApp.getFoldersByName("Myfolder");
  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];
      var v = [["Column1", "Column2", "Column3", "Column", "Column5"], ...values.map(r => {
        r[4] = `<a href="${r[4]}" target="_blank"><button type="button">Voir l'offre</button></a>`;
        return r;
      })];
      sheet.clearContents().getRange(1, 1, v.length, v[0].length).setValues(v);
    }
  }
}

CodePudding user response：

In your script, getValue() and setValue() are used in the loop. In this case, the process cost will become high. In your script, how about the following modification?

From:

// 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>');
    }

To:

// var spreadsheet = SpreadsheetApp.getActiveSheet(); // In order to use this in your script, please remove this line.
var [, ...dataRange] = spreadsheet.getDataRange().getValues();
var values = dataRange.map(r => [`<a href="${r[4]}" target="_blank"><button type="button">Button</button></a>`]);
spreadsheet.getRange(2, 5, values.length).setValues(values);

When this script is run, an array for putting to the sheet is created and put the array to the sheet. By this, the process cost of the script can be reduced.

References:

Added:

I thought that when I saw your added script, it is required to modify for reducing the process cost. So, how about the following modification?

Modified script:

function myfunction() {
  var keywords = ["valuetoremove1", "valuetoremove2"]; //  filter the column "C".
  var csvUrl = "https://myurl";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent, ";");
  var root = DriveApp.getFoldersByName("Myfoldername");
  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];

      var v = [["Column1", "Column2", "Column3", "Column", "Column5"], ...values.map(([...r]) => {
        r[4] = `<a href="${r[4]}" target="_blank"><button type="button">Voir l'offre</button></a>`;
        return r;
      })];

      // Or, if the above modification was not useful, please test the following script instead of it.
      // var v = [["Column1", "Column2", "Column3", "Column", "Column5"], ...values.map(r => {
      //   const temp = r.slice();
      //   temp[4] = `<a href="${temp[4]}" target="_blank"><button type="button">Voir l'offre</button></a>`;
      //   return temp;
      // })];

      sheet.clearContents().getRange(1, 1, v.length, v[0].length).setValues(v);
    }
  }
}

About your reply of It means that one time the data is imported correctly but the E column is not being turned into a button and if I run the script again the E column is being turned into a button., when I tested this script, the values of column "E" has the values like <a href="###" target="_blank"><button type="button">Voir l'offre</button></a>.
- By the way, when I saw your reply of It means that one time the data is imported correctly but the E column is not being turned into a button and if I run the script again the E column is being turned into a button. again, I thought that you might have wanted to put a button by creating HTML to a cell. If my understanding is correct, that cannot be achieved, because of the current specification. So, in this modification, I put the hyperlink to the column "E"?
- If you want to put the value like <a href="###" target="_blank"><button type="button">Voir l'offre</button></a> to the column "E", please modify r[4] = `=HYPERLINK("${r[4]}","Voir l'offre")`; to r[4] = `<a href="${r[4]}" target="_blank"><button type="button">Voir l'offre</button></a>`;.