Add link to entire column with appsscript-CodePudding

I'm retrieving information from an API and have to link the ID to the website.

I have the following code that is working but right now I'm retrieving 150 results and are expecting to have over 650. With 150 it takes two minutes to iterate through the API call and this for.

  for (var i = 2; i <= count; i  ) {
    var forRange = sheet.getRange("A" i)
    var forValue = forRange.getValue();
    const richText = SpreadsheetApp.newRichTextValue()
        .setText(forValue)
        .setLinkUrl('https://link.com/view.php?id='   forValue)
        .build();
    forRange.setRichTextValue(richText);
  }

My question is: Is there a more optimized way to do this iteration?

CodePudding user response：

In your script, getValue() and setRichTextValue() are used in a loop. In this case, the process cost will become high. I think that this is the reason for your issue. In this case, how about the following modification?

Modified script:

const sheet = SpreadsheetApp.getActiveSheet(); // Please use your Sheet object here.

const range = sheet.getRange("A2:A"   sheet.getLastRow());
const values = range.getDisplayValues();
const obj = range.getRichTextValues().map(([a], i) => [a.copy().setText(values[i][0]).setLinkUrl('https://link.com/view.php?id='   values[i][0]).build()]);
range.setRichTextValues(obj);

When this script is run, the object of RichTextValue is created for each row as an array. And, the created RichTextValue is used with setRichTextValues. By this, I thought that the process cost can be reduced.

Note:

In the current stage, getRichTextValues() cannot retrieve the number value from the cells by the current specification. Ref When I saw your sample Spreadsheet, it was found that the values of column "A" are the number values. This is the current issue. So, I modified my proposed script.

Modified script:

Note:

References: