使用Google Apps Script将Google Sheet链接以超链接形式插入Google Doc模板
2026-10-6
代码修改说明
你只需要按照下面的调整替换原有代码即可实现需求，原有不需要加超链接的占位符逻辑完全保留，仅修改超链接相关的部分：
核心修改点
- 修正超链接获取逻辑：原代码
getRichTextValue()仅支持读取单个单元格的富文本，改为getRichTextValues()批量获取所有单元格的富文本对象，可直接读取单元格内嵌的超链接地址 - 新增超链接替换辅助函数：解决原生
replaceText不支持插入超链接的问题 - 从
row[42]开始的占位符替换逻辑全部改为调用辅助函数，自动识别内容是普通文本还是URL，是URL自动转为可点击超链接
完整修改后代码
function onOpen() { const ui = SpreadsheetApp.getUi(); const menu = ui.createMenu('AutoFill Docs'); menu.addItem('Create New Docs', 'createNewGoogleDocs') menu.addToUi(); } // 新增辅助函数：替换占位符，有链接自动加超链接 function replaceTextWithLink(body, placeholder, text, linkUrl) { // 查找占位符位置 const rangeElement = body.findText(placeholder); if (!rangeElement) return; const start = rangeElement.getStartOffset(); const element = rangeElement.getElement().asText(); // 替换占位符为目标文本 element.deleteText(start, start + placeholder.length - 1); if (!text) return; element.insertText(start, text); // 有有效链接就加超链接，或者文本本身是URL格式也自动加 const url = linkUrl || (text.startsWith('http') ? text : null); if (url) { element.setLinkUrl(start, start + text.length - 1, url); } } function createNewGoogleDocs() { const googleDocTemplate = DriveApp.getFileById('1Olth1o_dIRZm6dpiltAesm5Q0ZeEFFb__6K5nVp26dA'); const destinationFolder = DriveApp.getFolderById('1O_j7K3RM6kXBttIPyNxe9o6kzI8N5ORM') const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data') const rows = sheet.getDataRange().getDisplayValues(); // 替换原来的url获取逻辑，批量获取所有单元格富文本 const richTextValues = sheet.getDataRange().getRichTextValues(); rows.forEach(function(row, index) { if (index === 0) return; if(row[0]) return const copy = googleDocTemplate.makeCopy(`${row[1]} ${row[2]} | Beta` , destinationFolder); const doc = DocumentApp.openById(copy.getId()) const body = doc.getBody(); // 以下原有不需要加超链接的逻辑保持不变 body.replaceText('{{First Name}}', row[1]); body.replaceText('{{Last Name}}', row[2]); body.replaceText('{{Email}}', row[3]); body.replaceText('{{Result}}', row[4]); body.replaceText('{{Mark}}', row[5]); body.replaceText('{{TopStrengthOneResult}}', row[6]); body.replaceText('{{TopStrengthOneDifficultyLevel}}', row[7]); body.replaceText('{{TopStrengthTwoResult}}', row[8]); body.replaceText('{{TopStrengthTwoDifficultyLevel}}', row[9]); body.replaceText('{{TopStrengthThreeResult}}', row[10]); body.replaceText('{{TopStrengthThreeDifficultyLevel}}', row[11]); body.replaceText('{{TopStrengthFourResult}}', row[12]); body.replaceText('{{TopStrengthFourDifficultyLevel}}', row[13]); body.replaceText('{{TopStrengthFiveResult}}', row[14]); body.replaceText('{{TopStrengthFiveDifficultyLevel}}', row[15]); body.replaceText('{{TopStrengthSixResult}}', row[16]); body.replaceText('{{TopStrengthSixDifficultyLevel}}', row[17]); body.replaceText('{{TopCategoryStrengthOne}}', row[18]); body.replaceText('{{TopCategoryStrengthTwo}}', row[19]); body.replaceText('{{TopCategoryStrengthThree}}', row[20]); body.replaceText('{{TopCategoryStrengthFour}}', row[21]); body.replaceText('{{TopCategoryStrengthFive}}', row[22]); body.replaceText('{{TopCategoryStrengthSix}}', row[23]); body.replaceText('{{TopWeaknessOneDifficultyLevel}}', row[24]); body.replaceText('{{TopWeaknessOneResult}}', row[25]); body.replaceText('{{TopWeaknessTwoDifficultyLevel}}', row[26]); body.replaceText('{{TopWeaknessTwoResult}}', row[27]); body.replaceText('{{TopWeaknessThreeDifficultyLevel}}', row[28]); body.replaceText('{{TopWeaknessThreeResult}}', row[29]); body.replaceText('{{TopWeaknessFourDifficultyLevel}}', row[30]); body.replaceText('{{TopWeaknessFourResult}}', row[31]); body.replaceText('{{TopWeaknessFiveDifficultyLevel}}', row[32]); body.replaceText('{{TopWeaknessFiveResult}}', row[33]); body.replaceText('{{TopWeaknessSixDifficultyLevel}}', row[34]); body.replaceText('{{TopWeaknessSixResult}}', row[35]); body.replaceText('{{TopCategoryWeaknessOne}}', row[36]); body.replaceText('{{TopCategoryWeaknessTwo}}', row[37]); body.replaceText('{{TopCategoryWeaknessThree}}', row[38]); body.replaceText('{{TopCategoryWeaknessFour}}', row[39]); body.replaceText('{{TopCategoryWeaknessFive}}', row[40]); body.replaceText('{{TopCategoryWeaknessSix}}', row[41]); // 以下从row[42]开始的占位符全部改为调用辅助函数 replaceTextWithLink(body, '{{StrengthOneFoundationalA}}', row[42], richTextValues[index][42].getLinkUrl()); replaceTextWithLink(body, '{{StrengthOneFoundationalB}}', row[43], richTextValues[index][43].getLinkUrl()); replaceTextWithLink(body, '{{StrengthOneFoundationalC}}', row[44], richTextValues[index][44].getLinkUrl()); replaceTextWithLink(body, '{{StrengthOneIntermediateA}}', row[45], richTextValues[index][45].getLinkUrl()); replaceTextWithLink(body, '{{StrengthOneIntermediateB}}', row[46], richTextValues[index][46].getLinkUrl()); replaceTextWithLink(body, '{{StrengthOneIntermediateC}}', row[47], richTextValues[index][47].getLinkUrl()); replaceTextWithLink(body, '{{StrengthOneAdvancedA}}', row[48], richTextValues[index][48].getLinkUrl()); replaceTextWithLink(body, '{{StrengthOneAdvancedB}}', row[49], richTextValues[index][49].getLinkUrl()); replaceTextWithLink(body, '{{StrengthOneAdvancedC}}', row[50], richTextValues[index][50].getLinkUrl()); replaceTextWithLink(body, '{{StrengthTwoFoundationalA}}', row[51], richTextValues[index][51].getLinkUrl()); replaceTextWithLink(body, '{{StrengthTwoFoundationalB}}', row[52], richTextValues[index][52].getLinkUrl()); replaceTextWithLink(body, '{{StrengthTwoFoundationalC}}', row[53], richTextValues[index][53].getLinkUrl()); replaceTextWithLink(body, '{{StrengthTwoIntermediateA}}', row[54], richTextValues[index][54].getLinkUrl()); replaceTextWithLink(body, '{{StrengthTwoIntermediateB}}', row[55], richTextValues[index][55].getLinkUrl()); replaceTextWithLink(body, '{{StrengthTwoIntermediateC}}', row[56], richTextValues[index][56].getLinkUrl()); replaceTextWithLink(body, '{{StrengthTwoAdvancedA}}', row[57], richTextValues[index][57].getLinkUrl()); replaceTextWithLink(body, '{{StrengthTwoAdvancedB}}', row[58], richTextValues[index][58].getLinkUrl()); replaceTextWithLink(body, '{{StrengthTwoAdvancedC}}', row[59], richTextValues[index][59].getLinkUrl()); replaceTextWithLink(body, '{{StrengthThreeFoundationalA}}', row[60], richTextValues[index][60].getLinkUrl()); replaceTextWithLink(body, '{{StrengthThreeFoundationalB}}', row[61], richTextValues[index][61].getLinkUrl()); replaceTextWithLink(body, '{{StrengthThreeFoundationalC}}', row[62], richTextValues[index][62].getLinkUrl()); replaceTextWithLink(body, '{{StrengthThreeIntermediateA}}', row[63], richTextValues[index][63].getLinkUrl()); replaceTextWithLink(body, '{{StrengthThreeIntermediateB}}', row[64], richTextValues[index][64].getLinkUrl()); replaceTextWithLink(body, '{{StrengthThreeIntermediateC}}', row[65], richTextValues[index][65].getLinkUrl()); replaceTextWithLink(body, '{{StrengthThreeAdvancedA}}', row[66], richTextValues[index][66].getLinkUrl()); replaceTextWithLink(body, '{{StrengthThreeAdvancedB}}', row[67], richTextValues[index][67].getLinkUrl()); replaceTextWithLink(body, '{{StrengthThreeAdvancedC}}', row[68], richTextValues[index][68].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFourFoundationalA}}', row[69], richTextValues[index][69].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFourFoundationalB}}', row[70], richTextValues[index][70].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFourFoundationalC}}', row[71], richTextValues[index][71].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFourIntermediateA}}', row[72], richTextValues[index][72].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFourIntermediateB}}', row[73], richTextValues[index][73].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFourIntermediateC}}', row[74], richTextValues[index][74].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFourAdvancedA}}', row[75], richTextValues[index][75].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFourAdvancedB}}', row[76], richTextValues[index][76].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFourAdvancedC}}', row[77], richTextValues[index][77].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFiveFoundationalA}}', row[78], richTextValues[index][78].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFiveFoundationalB}}', row[79], richTextValues[index][79].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFiveFoundationalC}}', row[80], richTextValues[index][80].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFiveIntermediateA}}', row[81], richTextValues[index][81].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFiveIntermediateB}}', row[82], richTextValues[index][82].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFiveIntermediateC}}', row[83], richTextValues[index][83].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFiveAdvancedA}}', row[84], richTextValues[index][84].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFiveAdvancedB}}', row[85], richTextValues[index][85].getLinkUrl()); replaceTextWithLink(body, '{{StrengthFiveAdvancedC}}', row[86], richTextValues[index][86].getLinkUrl()); replaceTextWithLink(body, '{{StrengthSixFoundationalA}}', row[87], richTextValues[index][87].getLinkUrl()); replaceTextWithLink(body, '{{StrengthSixFoundationalB}}', row[88], richTextValues[index][88].getLinkUrl()); replaceTextWithLink(body, '{{StrengthSixFoundationalC}}', row[89], richTextValues[index][89].getLinkUrl()); replaceTextWithLink(body, '{{StrengthSixIntermediateA}}', row[90], richTextValues[index][90].getLinkUrl()); replaceTextWithLink(body, '{{StrengthSixIntermediateB}}', row[91], richTextValues[index][91].getLinkUrl()); replaceTextWithLink(body, '{{StrengthSixIntermediateC}}', row[92], richTextValues[index][92].getLinkUrl()); replaceTextWithLink(body, '{{StrengthSixAdvancedA}}', row[93], richTextValues[index][93].getLinkUrl()); replaceTextWithLink(body, '{{StrengthSixAdvancedB}}', row[94], richTextValues[index][94].getLinkUrl()); replaceTextWithLink(body, '{{StrengthSixAdvancedC}}', row[95], richTextValues[index][95].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessOneFoundationalA}}', row[96], richTextValues[index][96].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessOneFoundationalB}}', row[97], richTextValues[index][97].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessOneFoundationalC}}', row[98], richTextValues[index][98].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessOneIntermediateA}}', row[99], richTextValues[index][99].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessOneIntermediateB}}', row[100], richTextValues[index][100].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessOneIntermediateC}}', row[101], richTextValues[index][101].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessOneAdvancedA}}', row[102], richTextValues[index][102].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessOneAdvancedB}}', row[103], richTextValues[index][103].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessOneAdvancedC}}', row[104], richTextValues[index][104].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessTwoFoundationalA}}', row[105], richTextValues[index][105].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessTwoFoundationalB}}', row[106], richTextValues[index][106].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessTwoFoundationalC}}', row[107], richTextValues[index][107].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessTwoIntermediateA}}', row[108], richTextValues[index][108].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessTwoIntermediateB}}', row[109], richTextValues[index][109].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessTwoIntermediateC}}', row[110], richTextValues[index][110].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessTwoAdvancedA}}', row[111], richTextValues[index][111].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessTwoAdvancedB}}', row[112], richTextValues[index][112].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessTwoAdvancedC}}', row[113], richTextValues[index][113].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessThreeFoundationalA}}', row[114], richTextValues[index][114].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessThreeFoundationalB}}', row[115], richTextValues[index][115].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessThreeFoundationalC}}', row[116], richTextValues[index][116].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessThreeIntermediateA}}', row[117], richTextValues[index][117].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessThreeIntermediateB}}', row[118], richTextValues[index][118].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessThreeIntermediateC}}', row[119], richTextValues[index][119].getLinkUrl()); replaceTextWithLink(body, '{{WeaknessThreeAdvancedA}}', row[120], richTextValues[index][120].
相关产品推荐
相关产品推荐
