The table (master) has links that point to cells within the table.
When creating a copy of this table (master copy), these links lead to the original table (master).
How can I automatically change the links so that they in the copy (master copy) refer to cells inside the copy (master copy)?
I'll link the script to the "fix links" button.
Link to table - https://docs.google.com/spreadsheets/d/1Jrpjm7Yjwp-3WTFKyBz87laPeq6ksjwPaaiWWNcIoow/edit?usp=sharing
I think it can be done by replacing #gid (sheetID) in the links. I found information how to get #gid (sheetID) - Google Apps Script, Google Sheets - Getting Spreadsheet ID and Sheet ID programmatically
But I don't know how to automatically find and replace all #gid (sheetID) in the current table in the links.
You can do it manually using Find and Replace. But it needs to be automated.
CodePudding user response：
I believe your goal is as follows.
- From your provided sample Spreadsheet, you want to change the hyperlinks in the range of "I6:I10" with the active sheet instead of the original sheet.
In this case, how about the following sample script?
Sample script 1:
Please copy and paste the following script to the script editor of Spreadsheet and save the script. And, as a test, please copy master, and please open the copied sheet. And then, please run this script. By this, the hyperlinks in the range of "I6:I10" are changed from the original sheet to the currently active sheet.
function myFunction() {
const checkRange = "I6:I10"; // This is from yoru sample Spreadsheet.
const sheet = SpreadsheetApp.getActiveSheet();
const sheetId = sheet.getSheetId();
const range = sheet.getRange(checkRange);
const richTextValues = range.getRichTextValues().map(r => r.map(c => {
const link = c.getLinkUrl();
return link ? c.copy().setLinkUrl(link.replace(/#gid\=.*&/, `#gid=${sheetId}`)).build() : c;
}));
range.setRichTextValues(richTextValues);
}
Sample script 2:
In this sample script, "master" sheet is copied to the same Spreadsheet with the specific sheet name, and the hyperlinks in the range of "I6:I10" are changed from the original sheet to the currently active sheet.
function myFunction2() {
const newSheetName = "sample"; // Please set the new sheet name by copying "master" sheet.
const checkRange = "I6:I10"; // This is from yoru sample Spreadsheet.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName("master");
const newSheet = sourceSheet.copyTo(ss).setName(newSheetName);
const sheetId = newSheet.getSheetId();
const range = newSheet.getRange(checkRange);
const richTextValues = range.getRichTextValues().map(r => r.map(c => {
const link = c.getLinkUrl();
return link ? c.copy().setLinkUrl(link.replace(/#gid\=.*&/, `#gid=${sheetId}`)).build() : c;
}));
range.setRichTextValues(richTextValues);
}