javascript - 从各种 Google 表格单元格中提取不同的链接,使用 Google Apps 脚本将它们粘贴到 Google 文档模板中作为活动 URL/超链接
问题描述
我想使用 Google Apps 脚本实现以下目标:
- 如果单元格是文本,则在占位符中显示文本(通过以下代码实现)
- 如果单元格是 URL,则将其显示为活动 URL 或超链接 URL - 目前,此代码也将 URL 提取为文本字符串。
任何帮助将不胜感激,因为我仍然是这门语言的初学者。我知道占位符的引用可能不是提取大量单元格的一种非常有效的方法,但我也没有足够的知识来更有效地实现它。
来自“body.replaceText('{{StrengthOneFoundationalA}}', row[42]);”的所有占位符 将返回文本链接或空白
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('AutoFill Docs');
menu.addItem('Create New Docs', 'createNewGoogleDocs')
menu.addToUi();
}
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();
const urls = sheet.getDataRange().getRichTextValue().getLinkUrl();
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]);
body.replaceText('{{StrengthOneFoundationalA}}', row[42]);
body.replaceText('{{StrengthOneFoundationalB}}', row[43]);
body.replaceText('{{StrengthOneFoundationalC}}', row[44]);
body.replaceText('{{StrengthOneIntermediateA}}', row[45]);
body.replaceText('{{StrengthOneIntermediateB}}', row[46]);
body.replaceText('{{StrengthOneIntermediateC}}', row[47]);
body.replaceText('{{StrengthOneAdvancedA}}', row[48]);
body.replaceText('{{StrengthOneAdvancedB}}', row[49]);
body.replaceText('{{StrengthOneAdvancedC}}', row[50]);
body.replaceText('{{StrengthTwoFoundationalA}}', row[51]);
body.replaceText('{{StrengthTwoFoundationalB}}', row[52]);
body.replaceText('{{StrengthTwoFoundationalC}}', row[53]);
body.replaceText('{{StrengthTwoIntermediateA}}', row[54]);
body.replaceText('{{StrengthTwoIntermediateB}}', row[55]);
body.replaceText('{{StrengthTwoIntermediateC}}', row[56]);
body.replaceText('{{StrengthTwoAdvancedA}}', row[57]);
body.replaceText('{{StrengthTwoAdvancedB}}', row[58]);
body.replaceText('{{StrengthTwoAdvancedC}}', row[59]);
body.replaceText('{{StrengthThreeFoundationalA}}', row[60]);
body.replaceText('{{StrengthThreeFoundationalB}}', row[61]);
body.replaceText('{{StrengthThreeFoundationalC}}', row[62]);
body.replaceText('{{StrengthThreeIntermediateA}}', row[63]);
body.replaceText('{{StrengthThreeIntermediateB}}', row[64]);
body.replaceText('{{StrengthThreeIntermediateC}}', row[65]);
body.replaceText('{{StrengthThreeAdvancedA}}', row[66]);
body.replaceText('{{StrengthThreeAdvancedB}}', row[67]);
body.replaceText('{{StrengthThreeAdvancedC}}', row[68]);
body.replaceText('{{StrengthFourFoundationalA}}', row[69]);
body.replaceText('{{StrengthFourFoundationalB}}', row[70]);
body.replaceText('{{StrengthFourFoundationalC}}', row[71]);
body.replaceText('{{StrengthFourIntermediateA}}', row[72]);
body.replaceText('{{StrengthFourIntermediateB}}', row[73]);
body.replaceText('{{StrengthFourIntermediateC}}', row[74]);
body.replaceText('{{StrengthFourAdvancedA}}', row[75]);
body.replaceText('{{StrengthFourAdvancedB}}', row[76]);
body.replaceText('{{StrengthFourAdvancedC}}', row[77]);
body.replaceText('{{StrengthFiveFoundationalA}}', row[78]);
body.replaceText('{{StrengthFiveFoundationalB}}', row[79]);
body.replaceText('{{StrengthFiveFoundationalC}}', row[80]);
body.replaceText('{{StrengthFiveIntermediateA}}', row[81]);
body.replaceText('{{StrengthFiveIntermediateB}}', row[82]);
body.replaceText('{{StrengthFiveIntermediateC}}', row[83]);
body.replaceText('{{StrengthFiveAdvancedA}}', row[84]);
body.replaceText('{{StrengthFiveAdvancedB}}', row[85]);
body.replaceText('{{StrengthFiveAdvancedC}}', row[86]);
body.replaceText('{{StrengthSixFoundationalA}}', row[87]);
body.replaceText('{{StrengthSixFoundationalB}}', row[88]);
body.replaceText('{{StrengthSixFoundationalC}}', row[89]);
body.replaceText('{{StrengthSixIntermediateA}}', row[90]);
body.replaceText('{{StrengthSixIntermediateB}}', row[91]);
body.replaceText('{{StrengthSixIntermediateC}}', row[92]);
body.replaceText('{{StrengthSixAdvancedA}}', row[93]);
body.replaceText('{{StrengthSixAdvancedB}}', row[94]);
body.replaceText('{{StrengthSixAdvancedC}}', row[95]);
body.replaceText('{{WeaknessOneFoundationalA}}', row[96]);
body.replaceText('{{WeaknessOneFoundationalB}}', row[97]);
body.replaceText('{{WeaknessOneFoundationalC}}', row[98]);
body.replaceText('{{WeaknessOneIntermediateA}}', row[99]);
body.replaceText('{{WeaknessOneIntermediateB}}', row[100]);
body.replaceText('{{WeaknessOneIntermediateC}}', row[101]);
body.replaceText('{{WeaknessOneAdvancedA}}', row[102]);
body.replaceText('{{WeaknessOneAdvancedB}}', row[103]);
body.replaceText('{{WeaknessOneAdvancedC}}', row[104]);
body.replaceText('{{WeaknessTwoFoundationalA}}', row[105]);
body.replaceText('{{WeaknessTwoFoundationalB}}', row[106]);
body.replaceText('{{WeaknessTwoFoundationalC}}', row[107]);
body.replaceText('{{WeaknessTwoIntermediateA}}', row[108]);
body.replaceText('{{WeaknessTwoIntermediateB}}', row[109]);
body.replaceText('{{WeaknessTwoIntermediateC}}', row[110]);
body.replaceText('{{WeaknessTwoAdvancedA}}', row[111]);
body.replaceText('{{WeaknessTwoAdvancedB}}', row[112]);
body.replaceText('{{WeaknessTwoAdvancedC}}', row[113]);
body.replaceText('{{WeaknessThreeFoundationalA}}', row[114]);
body.replaceText('{{WeaknessThreeFoundationalB}}', row[115]);
body.replaceText('{{WeaknessThreeFoundationalC}}', row[116]);
body.replaceText('{{WeaknessThreeIntermediateA}}', row[117]);
body.replaceText('{{WeaknessThreeIntermediateB}}', row[118]);
body.replaceText('{{WeaknessThreeIntermediateC}}', row[119]);
body.replaceText('{{WeaknessThreeAdvancedA}}', row[120]);
body.replaceText('{{WeaknessThreeAdvancedB}}', row[121]);
body.replaceText('{{WeaknessThreeAdvancedC}}', row[122]);
body.replaceText('{{WeaknessFourFoundationalA}}', row[123]);
body.replaceText('{{WeaknessFourFoundationalB}}', row[124]);
body.replaceText('{{WeaknessFourFoundationalC}}', row[125]);
body.replaceText('{{WeaknessFourIntermediateA}}', row[126]);
body.replaceText('{{WeaknessFourIntermediateB}}', row[127]);
body.replaceText('{{WeaknessFourIntermediateC}}', row[128]);
body.replaceText('{{WeaknessFourAdvancedA}}', row[129]);
body.replaceText('{{WeaknessFourAdvancedB}}', row[130]);
body.replaceText('{{WeaknessFourAdvancedC}}', row[131]);
body.replaceText('{{WeaknessFiveFoundationalA}}', row[132]);
body.replaceText('{{WeaknessFiveFoundationalB}}', row[133]);
body.replaceText('{{WeaknessFiveFoundationalC}}', row[134]);
body.replaceText('{{WeaknessFiveIntermediateA}}', row[135]);
body.replaceText('{{WeaknessFiveIntermediateB}}', row[136]);
body.replaceText('{{WeaknessFiveIntermediateC}}', row[137]);
body.replaceText('{{WeaknessFiveAdvancedA}}', row[138]);
body.replaceText('{{WeaknessFiveAdvancedB}}', row[139]);
body.replaceText('{{WeaknessFiveAdvancedC}}', row[140]);
body.replaceText('{{WeaknessSixFoundationalA}}', row[141]);
body.replaceText('{{WeaknessSixFoundationalB}}', row[142]);
body.replaceText('{{WeaknessSixFoundationalC}}', row[143]);
body.replaceText('{{WeaknessSixIntermediateA}}', row[144]);
body.replaceText('{{WeaknessSixIntermediateB}}', row[145]);
body.replaceText('{{WeaknessSixIntermediateC}}', row[146]);
body.replaceText('{{WeaknessSixAdvancedA}}', row[147]);
body.replaceText('{{WeaknessSixAdvancedB}}', row[148]);
body.replaceText('{{WeaknessSixAdvancedC}}', row[149]);
doc.saveAndClose();
const url = doc.getUrl();
sheet.getRange(index + 1, 1).setValue(url)
})
}
解决方案
不确定我是否理解正确。如果您需要从某个范围(例如从列)获取链接(http ...)或文本,可以通过以下方式完成:
const values = range.getRichTextValues().flat()
.map(x => x = x.getLinkUrl() || x.getText());
它尝试获取 URL,如果没有 URL,它会为范围的每个单元格获取文本。
如果您不仅有一个列,还有一个二维数组,那么当然应该有额外的步骤。但是核心算法是一样的:获取富文本,尝试从中获取url,如果url == null,获取文本。
如果您分享您的约会示例,则更容易弄清楚。
推荐阅读
- swift - 如何从 iPhone swift 获取所有文档和笔记?
- php - 在中间剪掉一部分字符串,但保留开始和结束
- python - python3用单个数值打印\
- c++ - 运算符将常量 int 添加到变量
- amazon-web-services - 如何区分s3中的文件夹和前缀
- dictionary - Mapster 和字典列表 System.ArgumentNullException: '值不能为空。参数名称:key'
- amazon-web-services - 具有自动缩放组的多个模板文件和使用 Terraform 的启动配置
- javascript - 无法将日期从 UTC 转换为本地日期
- django - 找不到 Heroku 管理页面
- maven - 如何告诉 Maven 始终下载特定的 jar