I'm wondering if theres anyway to shorten this code. I need to find and replace a bunch of phrases with the hyperlinked version of the phrase, and I'm hoping there is a more efficient way to do it. Thank you in advance!!
function insertUrl() {
let ranges = SpreadsheetApp.getActive()
.createTextFinder("Example 1")
.matchEntireCell(true)
.matchCase(true)
.matchFormulaText(false)
.ignoreDiacritics(true)
.findAll();
ranges.forEach(function(range){
range.setFormula('=HYPERLINK("https://www.website1.com/","Example 1")');
}
);
{
let ranges = SpreadsheetApp.getActive()
.createTextFinder("Example 1")
.matchEntireCell(true)
.matchCase(true)
.matchFormulaText(false)
.ignoreDiacritics(true)
.findAll();
ranges.forEach(function(range){
range.setFormula('=HYPERLINK("https://www.website2.com/","Example 1")');
}
);
}
{
let ranges = SpreadsheetApp.getActive()
.createTextFinder("Example 3")
.matchEntireCell(true)
.matchCase(true)
.matchFormulaText(false)
.ignoreDiacritics(true)
.findAll();
ranges.forEach(function(range){
range.setFormula('=HYPERLINK("https://www.website3.com/","Example 3")');
}
);
}
}
Make a function that takes in the URL and the text to find as parameters.
function insertUrl(text, url) {
SpreadsheetApp.getActive()
.createTextFinder(text)
.matchEntireCell(true)
.matchCase(true)
.matchFormulaText(false)
.ignoreDiacritics(true)
.findAll()
.forEach(function (range) {
range.setFormula('=HYPERLINK(' + url + ',"' + text + '")');
});
}
insertUrl("Example 1", "https://www.website1.com/");
insertUrl("Example 1", "https://www.website2.com/");
insertUrl("Example 3", "https://www.website3.com/");
Was your use of Example 1 with website2.com a typo? You probably meant to use Example 2 there instead.
I believe your goal is as follows.
In your situation, how about the following modification?
Please confirm the value of ar. If your actual situation is different from the sample value, please modify them.
function insertUrl() {
// Please set the text and the formula you want to use.
const ar = [
{ text: "Example 1", formula: '=HYPERLINK("https://www.website1.com/","Example 1")' },
{ text: "Example 1", formula: '=HYPERLINK("https://www.website2.com/","Example 1")' },
{ text: "Example 3", formula: '=HYPERLINK("https://www.website3.com/","Example 3")' },
];
const ss = SpreadsheetApp.getActive();
ar.forEach(({ text, formula }) =>
ss.createTextFinder(text)
.matchEntireCell(true)
.matchCase(true)
.matchFormulaText(false)
.ignoreDiacritics(true)
.replaceAllWith(formula)
);
}
In this modification, an array including the values you want to replace is created. And, the formula is put using replaceAllWith. By this, I thought that the process cost might be able to be reduced a little.
As another direction for reducing the process cost of your script, you can also use Sheets API as follows. In this case, please enable Sheets API at Advanced Google services.
function insertUrl() {
// Please set the text and the formula you want to use.
const ar = [
{ text: "Example 1", formula: '=HYPERLINK("https://www.website1.com/","Example 1")' },
{ text: "Example 1", formula: '=HYPERLINK("https://www.website2.com/","Example 1")' },
{ text: "Example 3", formula: '=HYPERLINK("https://www.website3.com/","Example 3")' },
];
const requests = ar.map(({ text, formula }) => ({ findReplace: { allSheets: true, find: text, replacement: formula, matchEntireCell: true, matchCase: true } }));
Sheets.Spreadsheets.batchUpdate({ requests }, SpreadsheetApp.getActive().getId());
}
Try and use literal notation
`=HYPERLINK("${url}","${text}")`
script
function insertURL() {
const changes = {
'qwant': "https://www.qwant.com/",
'google': "https://www.google.com/",
};
const ss = SpreadsheetApp.getActiveSpreadsheet();
const requests = Object.entries(changes).map(([text, url]) => ({
findReplace: {
find: text,
replacement: `=HYPERLINK("${url}","${text}")`,
matchEntireCell: true,
allSheets: true,
},
}));
Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
}
enable google sheets api service