How to format hyperlinks leaveOnlyLinks.gs

[Avito Bot]

It deletes the cell, but should leave links instead of text with hyperlinks

function leaveOnlyLinks() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var range = sheet.getRange("A:A");

  var formulas = range.getFormulas();

 

  for (var i = 0; i < formulas.length; i++) {

    var formula = formulas[i][0];

    if (formula.startsWith('=HYPERLINK')) {

      var link = formula.match(/"(.*?)"/)[1];

      formulas[i][0] = link;

    }

  }

 

  range.setFormulas(formulas);

}

[Avito Bot]

Here is an example, in the first column there is data with hyperlinks and only links need to be moved to the second column
As in the example with the third line

https://docs.google.com/spreadsheets/d/1ysy2P0DTkRXXitDwfAclzRl5VhSBKfJ8XyqMIkJR6oo/edit#gid=0

суббота, 14 октября 2023 г. в 14:14:26 UTC+3, Avito Bot:

[Tanaike]

About "Here is an example,", unfortunately, I cannot open your sample Spreadsheet. I apologize for this. Can you confirm it again?

[Avito Bot]

I'm sorry, I opened access

суббота, 14 октября 2023 г. в 15:58:00 UTC+3, Tanaike:

[Tanaike]

Thank you for replying. From your provided Spreadsheet, how about the following sample script?

function myFunction() {

  const sheetName = "Лист1"; // Please set your sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  const range = sheet.getRange("A2:A" + sheet.getLastRow());

  const urls = range.getRichTextValues().map(([a]) =>

    [a.getRuns().reduce((ar, r) => {

      const url = r.getLinkUrl();

      if (url) {

        ar.push(url);

      }

      return ar;

    }, []).join(",") || null]

  );

  range.offset(0, 1).setValues(urls);

}

When this script is run, the URLs are retrieved from column "A". And, the retrieved URLs are put into column "B".

This sample script can be used on your provided Spreadsheet. If you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

[Avito Bot]

Tanaike thank you very much for your attention to the problem, I am very glad that there are such smart and kind people in the world

function myFunction() {

  const sheetName = "Лист1"; // Please set your sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);// Получаем активную таблицу и выбираем лист с указанным названием

  const range = sheet.getRange("A2:A" + sheet.getLastRow()); // Выбираем диапазон данных

  const urls = range.getRichTextValues().map(([a]) => // Получаем значения ячеек в формате RichText и применяем функцию map для обработки каждого значения

    [a.getRuns().reduce((ar, r) => { // Получаем все ссылки внутри значения ячейки и объединяем их в одну строку, разделенную запятыми

      const url = r.getLinkUrl();

      if (url) {

        ar.push(url);

      }

      return ar;

    }, []).join(",") || null]

  );

  const destinationRange = sheet.getRange("B2:B" + (1 + urls.length)); // Указываем куда выводить

  destinationRange.setValues(urls); // Записываем значения

}

воскресенье, 15 октября 2023 г. в 03:30:34 UTC+3, Tanaike: