How can I move urls from sheet ranges while building the links at destinations using Google Apps Scr-CodePudding

I've been trying to accomplish it using the following function, but with no success:

function moveLinks() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('SetRichTextValues via Apps Script');
  const srcData = sheet.getRange("A2:E5").getValues();

  const destHeaders = sheet.getRange("G2:K5").getValues();
  const links1Col = destHeaders.indexOf('Links1');
  const links2Col = destHeaders.indexOf('Links2');
  const links3Col = destHeaders.indexOf('Links3');

  let urlColIndexes = [links1Col, links2Col, links3Col];
  urlColIndexes.forEach(function (colIndex) {
    let data = srcData;
    let col = colIndex;
    let builders = data.map(r => {
      let url = r[col];
      while (url !== null) {
        //if (url.indexOf('https') > -1) {
          let builder = SpreadsheetApp.newRichTextValue().setText('LINK').setLinkUrl(url).build();
          return [builder]
        //} else {
          return ['']
        //}
      }
    });
    sheet.getRange(3, colIndex   9, builders.length, 1).setRichTextValues(builders);
  })
}

This is how it looks like (UI):

In case you feel like playing with the code, the file name in the script editor is: SetRichTextValues()...in this Spreadsheet.

CodePudding user response：

Modification points:

In your script, const destHeaders = sheet.getRange("G2:K5").getValues() is 2 dimensional array. So, links1Col, links2Col, and links3Col are always -1.
And, from your showing image, it seems that const destHeaders = sheet.getRange("G2:K5").getValues() is the destination range. In your actual situation, the destination range has already had the header row at "G2:K2". In this answer, it supposes that your destination range has the header row.
setRichTextValues is used in a loop. In this case, the process cost will become high.

When these points are reflected in your script, how about the following modification?

Modified script:

This script uses Sheets API. So, please enable Sheets API at Advanced Google services.

function moveLinks() {
  // Retrieve values.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('SetRichTextValues via Apps Script');
  const srcData = sheet.getRange("A2:E5").getValues();
  const objSrcData = srcData[0].map((_, c) => srcData.map(r => r[c])).reduce((o, [h, ...r]) => (o[h] = r, o), {});
  const destHeaders = sheet.getRange("G2:K2").getValues()[0];

  // Cell values.
  const len = srcData[0].length;
  const t1 = destHeaders.map(h => objSrcData[h] || Array(len).fill(null));
  const cellValues = t1[0].map((_, c) => t1.map(r => r[c] || null));

  // RichTextValues.
  const noValue = SpreadsheetApp.newRichTextValue().setText("").build();
  const richTextValues = cellValues.map(r => destHeaders.map((h, i) => h.includes("Links") ? (r[i] ? SpreadsheetApp.newRichTextValue().setText('LINK').setLinkUrl(r[i]).build() : noValue) : noValue));

  // Put values.
  const range = sheet.getRange(3, 7, richTextValues.length, richTextValues[0].length);
  range.setRichTextValues(richTextValues);
  SpreadsheetApp.flush();
  Sheets.Spreadsheets.Values.update({ values: cellValues.map(r => destHeaders.map((h, i) => h.includes("Links") ? null : r[i])) }, ss.getId(), `'${sheet.getSheetName()}'!${range.getA1Notation()}`, { valueInputOption: "USER_ENTERED" });
}

When this script is run, the source values are retrieved from "A2:E5". And, the destination header row is retrieved from "G2:K2". And, the destination values are put to "G3:K".

Note:

In this modification, it supposes that the destination range has the header row, and the header titles are the same as the header titles of the source range. Please be careful about this.
In the current stage, setRichTextValues cannot use null object. So, in this modification, in order to put the cell values by low process cost, I used Sheets API.

If you don't want to use Sheets API, please modify the above script as follows.

From

  SpreadsheetApp.flush();
  Sheets.Spreadsheets.Values.update({ values: cellValues.map(r => destHeaders.map((h, i) => h.includes("Links") ? null : r[i])) }, ss.getId(), `'${sheet.getSheetName()}'!${range.getA1Notation()}`, { valueInputOption: "USER_ENTERED" });

  const v = cellValues[0].map((_, c) => cellValues.map(r => [r[c]]));
  destHeaders.forEach((h, i) => {
    if (!h.includes("Links")) sheet.getRange(3, i   7, v[i].length).setValues(v[i]);
  });

When you change the structure of your Spreadsheet, this modified script might not be able to be used. Please be careful about this.

Modification points:

Modified script:

Note:

References: