The "master" table has links to cells within that table. These are "I6:I10", "I212:I216", "I418:I422", "I625:I629", "I832:I836", "I1038:I1042". How can I change these references so that when creating a copy of the "master" table, they refer to cells inside the "master copy"? Maestro Tanaike wrote a script that works. But I wrote a little incorrectly what is required of the script. Need to modify it so that it processes the ranges "I6:I10", "I212:I216", "I418:I422", "I625:I629", "I832:I836", "I1038:I1042"

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);
}

Link to table - https://docs.google.com/spreadsheets/d/1Jrpjm7Yjwp-3WTFKyBz87laPeq6ksjwPaaiWWNcIoow/edit?usp=sharing

i tried to put in a script

const checkRange2 = "I212:I216";

but it doesn't work

CodePudding user response：

Use a RangeList, like this:

function replace() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const sheetId = sheet.getSheetId();
  const rangeList = sheet.getRangeList(['I6:I10', 'I212:I216', 'I418:I422', 'I625:I629', 'I832:I836', 'I1038:I1042']);
  rangeList.getRanges().forEach(range => modifyLinks_(range, sheetId));
}

function modifyLinks_(range, sheetId) {
  const richTextValues = range.getRichTextValues().map(row => row.map(value => {
    const link = value.getLinkUrl();
    return link ? value.copy().setLinkUrl(link.replace(/#gid\=.*&/, `#gid=${sheetId}`)).build() : value;
  }));
  range.setRichTextValues(richTextValues);
}