Skip to content

搞英语 → 看世界

翻译英文优质信息和名人推特

Menu
  • 首页
  • 独立博客
  • 专业媒体
  • 名人推特
  • 邮件列表
  • 关于本站
  • Product Hunt
  • Visual Capitalist
  • Elon Musk
Menu

如何从 Google 表格中的 HYPERLINK 函数中提取 URL

Posted on 2022-03-05

Google 表格的HYPERLINK 公式可让您在电子表格中插入超链接。该函数有两个参数:

  1. 链接的完整 URL
  2. 链接的描述或锚文本

URL 和锚文本可以指定为字符串或单元格引用。

如果使用HYPERLINK函数将超链接插入单元格,则无法直接从公式中提取 URL。您可以考虑编写一个复杂的正则表达式来匹配和提取单元格公式中的超链接,或者使用 Google Sheets API 的 Apps Script。

 const extractHyperlinksInSheet = ( ) => { const ss = SpreadsheetApp . getActiveSpreadsheet ( ) ; const sheet = SpreadsheetApp . getActiveSheet ( ) ; const hyperlinks = [ ] ; const spreadsheedId = ss . getId ( ) ; const sheetName = sheet . getName ( ) ; const getRange = ( row , col ) => { const address = sheet . getRange ( row + 1 , col + 1 ) . getA1Notation ( ) ; return ` ${ sheetName } ! ${ address } ` ; } ; const getHyperlink = ( rowIndex , colIndex ) => { const { sheets } = Sheets . Spreadsheets . get ( spreadsheedId , { ranges : [ getRange ( rowIndex , colIndex ) ] , fields : 'sheets(data(rowData(values(formattedValue,hyperlink))))' , } ) ; const [ { formattedValue , hyperlink } ] = sheets [ 0 ] . data [ 0 ] . rowData [ 0 ] . values ; hyperlinks . push ( { rowIndex , colIndex , formattedValue , hyperlink } ) ; } ; sheet . getDataRange ( ) . getFormulas ( ) . forEach ( ( dataRow , rowIndex ) => { dataRow . forEach ( ( cellValue , colIndex ) => { if ( / =HYPERLINK / i . test ( cellValue ) ) { getHyperlink ( rowIndex , colIndex ) ; } } ) ; } ) ; Logger . log ( hyperlinks ) ; } ;

另请参阅:用 RegEx 替换 Google Docs 中的文本

来源: https://www.labnol.org/code/extract-hyperlinks-google-sheets-220301

发表回复 取消回复

要发表评论,您必须先登录。

本站文章系自动翻译,站长会周期检查,如果有不当内容,请点此留言,非常感谢。
  • Articles on Jose M. (2)
  • Balaji S. Srinivasan (1)
  • Bob Nystrom (1)
  • Dan Wang (1)
  • dostoynikov (4)
  • Joel on Software (1)
  • John Resig (1)
  • Laurence Gellert's Blog (1)
  • Matt Might's blog (4)
  • Michael Feathers (1)
  • News Letter (206)
  • Noahpinion (9)
  • Philip Walton (1)
  • Pivotal (1)
  • Rohit Kumar (1)
  • Sam Julien (1)
  • Scott Hanselman's Blog (2)
  • Tom's blog (2)
  • Wait But Why (2)
  • 湾区日报 (22)
  • 英文媒体 (40,384)
    • Ars Technica (2,809)
    • Daily Infographic (337)
    • Engadget (6,192)
    • Enonomist (77)
    • FlowingData (279)
    • Hacker News (773)
    • Hacker News Daily (350)
    • Hacker Noon (125)
    • Harvard Health (155)
    • KK – Cool Tools (242)
    • KK – Recomendo (279)
    • Make Use Of (158)
    • NASA Astronomy Picture (307)
    • Product Hunt (8,505)
    • Psyche (236)
    • Quanta Magazine (199)
    • Science current issue (656)
    • Sidebar (1,152)
    • Singularity HUB (307)
    • TechCrunch (9,860)
    • The Practical Developer (99)
    • The Verge (6,866)
    • Visual Capitalist (421)
  • 英文推特 (17,685)
    • Bill Gates (342)
    • Brett Winton (1,333)
    • Cathie Wood (303)
    • Durov's Channel (25)
    • Elon Musk (5,422)
    • GeekWire (2,868)
    • Hunter Walk (57)
    • Mark Gurman (1,061)
    • Naval (699)
    • Parag Agrawal (52)
    • Ray Dalio (1,045)
    • Riccardo Mori (16)
    • Steph Smith (2,269)
    • Tim Cook (169)
    • Vitalik Buterin (2,024)
  • 英文独立博客 (4,231)
    • A learning a day (354)
    • A Smart Bear (2)
    • AddyOsmani.com (11)
    • Adwyat Krishna (30)
    • Ahmad Shadeed (2)
    • Alex Turek (3)
    • All Poetry (1)
    • All That is Solid (51)
    • André Staltz (4)
    • arxivblog (37)
    • Astral Codex Ten (15)
    • Atoms vs Bits (28)
    • AVC (45)
    • Basic Apple Guy (44)
    • Ben Thompson (13)
    • Benedict Evans (8)
    • Blog – storytelling with data (48)
    • Built For Mars (12)
    • Caleb Porzio (1)
    • Cameron Sun (2)
    • Christian Heilmann (42)
    • Christopher C (3)
    • Chun Tian (binghe) (1)
    • Codrops (23)
    • Cold Takes (16)
    • Dan Luu (1)
    • Daniel Lemire's blog (66)
    • David Amos (23)
    • David Perell (6)
    • David Walsh Blog (42)
    • Derek Sivers (30)
    • Desvl (16)
    • Devon's Site (5)
    • Digital Inspiration (28)
    • DKB Blog (4)
    • Douglas Vaghetti (12)
    • dropsafe (65)
    • DSHR (41)
    • Dunk (5)
    • DYNOMIGHT (41)
    • eagereyes (7)
    • Endless Metrics (135)
    • Entitled Opinions (8)
    • Exception Not Found (6)
    • Experimental History (25)
    • Farnam Street (6)
    • Fed Guy (11)
    • Felix Krause (3)
    • Florent Crivello (2)
    • Free Mind (7)
    • Full Stack Economics (40)
    • Funny JS (3)
    • Future A16Z (47)
    • Glassnode Insights (60)
    • Human Who Codes (5)
    • Infographics – Cool Infographics (12)
    • Information is Beautiful (13)
    • Irrational Exuberance (46)
    • Jacob Kaplan-Moss (13)
    • Jakob Greenfeld (51)
    • James Sinclair (3)
    • Jason Fried (21)
    • Jeff Kaufman (197)
    • John's internet house (31)
    • Johnny Rodgers (4)
    • Julia Evans (27)
    • Julian.com (2)
    • Kalzumeus (1)
    • Kevin Cox (13)
    • Kevin Norman (4)
    • KK – The Technium (51)
    • Krishna (7)
    • Lee Robinson (5)
    • Lines and Colors (53)
    • Lyn Alden – Investment Strategy (3)
    • Martin Fowler (30)
    • More To That (17)
    • Morgan Housel (81)
    • My Super Secret Diary (32)
    • Naval Blog (3)
    • Neckar's New Money (88)
    • Nick Whitaker (4)
    • Nicky's New Shtuff (1)
    • nutcroft (11)
    • Paul Graham (2)
    • Paul Graham: Essays (2)
    • Penguin Random House (91)
    • Phoenix's island (1)
    • Prof Galloway (37)
    • Python Weekly (34)
    • Rachel (35)
    • Real Life (34)
    • Sasha (66)
    • Science & technology (133)
    • Sébastien Dubois (6)
    • Secretum Secretorum (14)
    • Seth's Blog (184)
    • Shu Ding (3)
    • SignalFire (10)
    • Simon Willison's Weblog (251)
    • Simons Foundation (89)
    • SLIME MOLD TIME MOLD (24)
    • Slyar Home (8)
    • Spencer Greenberg (12)
    • Stay SaaSy (14)
    • Stephen Malina (4)
    • Stephen Wolfram Writings (2)
    • Strange Loop Canon (25)
    • Stratechery (11)
    • Tech Notes (11)
    • The Commonplace (30)
    • The Generalist (2)
    • The Intrinsic Perspective (31)
    • The Latest in Hearing Health | HeardThat (8)
    • The Mad Ned Memo (2)
    • The Rabbit Hole (38)
    • TLDR Newsletter (81)
    • Tomasz Tunguz (98)
    • Tony Kulesa (2)
    • Troy Hunt (68)
    • Tychlog (1)
    • Uncharted Territories (64)
    • Visualising Data (10)
    • Weichen Liu (21)
    • What's New (57)
    • Works in Progress (1)
    • Workspaces (32)
    • Writing (9)
    • Xe's Blog (42)
    • xkcd.com (133)
    • Yihui Xie (17)
    • Zoran Jambor (11)
©2023 搞英语 → 看世界 | Design: Newspaperly WordPress Theme