Paste special, values only but retain hyperlink in formula

Question

I'm using Google Sheets to track my weekly rankings. My method has been to import a new CSV as a separate sheet and use a master sheet to pull my weekly data into one place.

I've just hit the 2mil cell limit on my workbook and need to recreate my document to allow me to delete all the extra sheets and leave only the master sheet.

The problem is this, how can I use Paste special, values only to break the connection with the sheet from which data is referenced, BUT at the same time preserve the hyperlink portion of the formula that links the ranking position with the ranking page for that keyword.

In essence, I need my Sheet1 to remain the same, but allow me to delete Sheet2 (and the other 50 sheets I have in my real file).

The root problem is that keeping information in the URL attribute of a spreadsheet cell is a bad idea. It is near impossible to access or convert to another form. Even if you manage through the current crisis, there will be more in your future unless the root cause is addressed. Eventual data loss is likely. Solution for the root problem: delete Sheet1, keep the data as it is in Sheet 2. — user6655984
Hello @zaq, I realise I maybe asking for too much, but when you have ~60 columns x 300 rows just for a single year of data collection, you have to find a format to display it and make it usable. I use this table every single day to keep track and perform on the go analysis. — AudreyW
On the topic: I found this article, that offered a marco that evaluates portions of the formula and replaces them with the calculated value. However, what it does for me, is find the first VLOOKUP and replace it with it's value, but does nothing for the other two, which are contained in the HYPERLINK formula. Any ideas how I can tweak it to target the other ones as well. It's almost there, but I can't comprehend VBA so well. — AudreyW
Correction - The macro above calculates two VLOOKUPs which can mathematically evaluated to a number. Doesn't however calculate the one which queries the URL. — AudreyW

player0 player0 · Accepted Answer · 2019-05-25T22:48:09

not really possible in one go, but if you

add this script to your spreadsheet and then use this custom formula: =URL(A1)

function URL(reference) {
var sheet = SpreadsheetApp.getActiveSheet();
var formula = SpreadsheetApp.getActiveRange().getFormula();
var args = formula.match(/=\w+\((.*)\)/i);
try {
var range = sheet.getRange(args[1]);
}
catch(e) {
throw new Error(args[1] + ' is not a valid range');
}
var formulas = range.getFormulas();
var output = [];
for (var i = 0; i < formulas.length; i++) {
var row = [];
for (var j = 0; j < formulas[0].length; j++) {
  var url = formulas[i][j].match(/=hyperlink\("([^"]+)"/i);
  row.push(url ? url[1] : '');
}
output.push(row);
}
return output
}

or this indirect (shorter) one with a custom formula: =URL("A1")

function URL(input) {
var range = SpreadsheetApp.getActiveSheet().getRange(input);
var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1];
return url;
}

it will extract the link behind the hyperlink so you could copy it

Paste special, values only but retain hyperlink in formula

1 Answers