Add text-list to hyperlink cell in Google Spreadsheet-CodePudding

How can I add text from column A to page link in column B? So I would like to scrap part of page from hundreds of page, and I need to put text data on column A to ...com/[text from column A]. Maybe someone have a formula.

I've tried using Macro features, but didn't know how to loop my Macro using script editor. You can see the Macro script below.

/** @OnlyCurrentDoc */

function Untitledmacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B4').activate();
  spreadsheet.getCurrentCell().setRichTextValue(SpreadsheetApp.newRichTextValue()
  .setText('https://www.example.com/AHAP')
  .setTextStyle(0, 34, SpreadsheetApp.newTextStyle()
  .setForegroundColor('#1155cc')
  .setUnderline(true)
  .build())
  .build());
  spreadsheet.getRange('B5').activate();
  spreadsheet.getCurrentCell().setRichTextValue(SpreadsheetApp.newRichTextValue()
  .setText('https://www.example.com/AIMS')
  .setTextStyle(0, 34, SpreadsheetApp.newTextStyle()
  .setForegroundColor('#1155cc')
  .setUnderline(true)
  .build())
  .build());
  spreadsheet.getRange('B6').activate();
};

function Untitledmacro1() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().setRichTextValue(SpreadsheetApp.newRichTextValue()
  .setText('https://www.example.com/AISA')
  .setTextStyle(0, 34, SpreadsheetApp.newTextStyle()
  .setForegroundColor('#1155cc')
  .setUnderline(true)
  .build())
  .build());
  spreadsheet.getCurrentCell().offset(1, 0).activate();
  spreadsheet.getCurrentCell().setRichTextValue(SpreadsheetApp.newRichTextValue()
  .setText('https://www.example.com/AKKU')
  .setTextStyle(0, 34, SpreadsheetApp.newTextStyle()
  .setForegroundColor('#1155cc')
  .setUnderline(true)
  .build())
  .build());
  spreadsheet.getCurrentCell().offset(1, 0).activate();
};

CodePudding user response：

Formula option:

If you have text in column A and links in column B, you can use the following formula:

=ARRAYFORMULA(CONCAT(B1:B,A1:A))

With Google AppsScript:

If the link will be the same for all rows and you have text in column A, you can use the following script:

function makeLinks1() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');///Change Sheet1 to the name of your sheet
  let rangeWord = ss.getRange("A1:A").getValues().flat();
  let link =  'https://www.example.com/';

  rangeWord = rangeWord.filter((element) => {return (element !== '')});

  for (let i=0; i< rangeWord.length ; i  ){
    ss.getRange(i 1,2).setValue(link rangeWord[i]);
  };
}

If you have text in column A and links from column B are different, you can run the following script:

function makeLinks() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); ///Change Sheet1 to the name of your sheet
  let rangeWord = ss.getRange("A1:A").getValues().flat();
  let rangeLinks = ss.getRange("B1:B").getValues().flat();

  rangeWord = rangeWord.filter((element) => {return (element !== '')});
  rangeLinks = rangeLinks.filter((element) => {return (element !== '')});

  for (let i=0; i< rangeWord.length ; i  ){
    ss.getRange(i 1,3).setValue(rangeLinks[i] rangeWord[i]);
  };
}

CodePudding user response：

Try it this way:

function makelinks() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const shsr = 4;
  const url = "http://www.example.com/";
  const vs = sh.getRange(shsr,1,sh.getLastRow() - shsr   1,2).getValues();
  let vo = vs.map((r,i) => {
    return [`=HYPERLINK("${url}${r[0]}","${r[0]}")`];
  });
  sh.getRange(shsr,2,vo.length,vo[0].length).setFormulas(vo);
}