使用 Apps 腳本獲取 Confluence 頁面的內容並解析其內容

我相信你的目標如下。

• You want to parse the HTML from the sample value (it generates the content, but it is difficult to parse:) in your question and put them to the sheet.
  • You have already been able to retrieve the value from the API you want to use.

In this case, how about the following sample script? In this case, I would like to propose using Sheets API. The pasteData of Sheets API can parse the HTML table.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet. And, this script uses Sheets API. So please enable Sheets API at Advanced Google services. And, please set the sheet name. And run the function. By this, the Sheets API parses the HTML table and puts to the sheet.

function myFunction() {
  const obj = { "id": "2020706439", "type": "page", "status": "current", "title": "Onboard Resource Template for more than one resource", "macroRenderedOutput": {}, "body": { "storage": { "value": "<ac:structured-macro ac:name=\"info\" ac:schema-version=\"1\" ac:macro-id=\"756d4d54-7492-49ac-b502-19d1a740ea92\"><ac:rich-text-body><p>To use it, please make a copy of this template and replace the title with Resource Name(s) and add the onboarding date as a suffix</p></ac:rich-text-body></ac:structured-macro><table data-layout=\"wide\"><colgroup><col style=\"width: 182.0px;\" /><col style=\"width: 144.0px;\" /><col style=\"width: 148.0px;\" /><col style=\"width: 143.0px;\" /><col style=\"width: 137.0px;\" /><col style=\"width: 142.0px;\" /></colgroup><tbody><tr><td data-highlight-colour=\"#6fa8dc\" colspan=\"6\"><p style=\"text-align: center;\"><strong>New&nbsp;Resource&nbsp;(s) Onboarding&nbsp;Information and Tracking</strong></p></td></tr><tr><td data-highlight-colour=\"#ff9900\"><p>Information&nbsp;Provided&nbsp;by&nbsp;Vendor</p></td><td data-highlight-colour=\"#ff9900\"><p>Resource 1</p></td><td data-highlight-colour=\"#ff9900\"><p>Resource 2</p></td><td data-highlight-colour=\"#ff9900\"><p>Resource 3</p></td><td data-highlight-colour=\"#ff9900\"><p>Resource 4</p></td><td data-highlight-colour=\"#ff9900\"><p>Resource 5</p></td></tr><tr><td><p>First&nbsp;Name</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Last&nbsp;Name</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Address</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Phone&nbsp;Number</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>E-Mail</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>WFH&nbsp;or&nbsp;W@Office</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Location (if W@Office)</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Role</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Start&nbsp;Date</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>End&nbsp;Date</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Vendor's&nbsp;Name</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Vendor's&nbsp;Contact&nbsp;Name</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Vendor's&nbsp;Phone&nbsp;Number</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Manager's&nbsp;Name</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Team</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td data-highlight-colour=\"#b6d7a8\" colspan=\"6\"><p>Information&nbsp;Provided&nbsp;by&nbsp;Onboarding Manager</p></td></tr><tr><td><p>New&nbsp;Resource&nbsp;ID</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>New&nbsp;Resource&nbsp;Company&nbsp;E-mail</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Cost&nbsp;Center</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Laptop&nbsp;Serial&nbsp;Number</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td data-highlight-colour=\"#b6d7a8\"><p><ac:inline-comment-marker ac:ref=\"377a17e3-a869-451f-a58b-19cac76916a4\">Access&nbsp;Requests&nbsp;by&nbsp;Rosana</ac:inline-comment-marker></p></td><td data-highlight-colour=\"#b6d7a8\"><p style=\"text-align: center;\">Ticket&nbsp;Number</p></td><td data-highlight-colour=\"#b6d7a8\"><p style=\"text-align: center;\">Date&nbsp;</p><p style=\"text-align: center;\">Submitted</p></td><td data-highlight-colour=\"#b6d7a8\"><p>SNOW Assignee</p></td><td data-highlight-colour=\"#b6d7a8\"><p style=\"text-align: center;\">Ready&nbsp;in<ac:inline-comment-marker ac:ref=\"269ae226-6036-4ede-9720-d3c25f5d31d2\">&nbsp;</ac:inline-comment-marker>(n)-Days</p></td><td data-highlight-colour=\"#b6d7a8\"><p style=\"text-align: center;\">Date Completed</p></td></tr><tr><td><p>Company&nbsp;ID&nbsp;Request</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Laptop</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Slack</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Outlook</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>WebEx</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>Ping</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td data-highlight-colour=\"#a2c4c9\" colspan=\"6\"><p>Access&nbsp;Request&nbsp;by&nbsp;Program Manager (or a PM on his behalf)</p></td></tr><tr><td><p>SNOW Access and Timesheets</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td data-highlight-colour=\"#f4cccc\" colspan=\"6\"><p>Access Request/Action by&nbsp;David&nbsp;Leal</p></td></tr><tr><td><p>Access to Jira/Confluence</p></td><td><p /></td><td><p /></td><td><p>Jira Admin</p></td><td><p /></td><td><p /></td></tr><tr><td><p>Resource&nbsp;Plan</p></td><td><p /></td><td><p /></td><td><p>Snow Manager</p></td><td><p /></td><td><p /></td></tr><tr><td><p><em>Actions</em></p></td><td data-highlight-colour=\"#f4f5f7\" colspan=\"5\"><p style=\"text-align: center;\"><em> Date Completed</em></p></td></tr><tr><td><p style=\"text-align: right;\">Add to Slack&nbsp;Channels</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p style=\"text-align: right;\">Add to JIRA&nbsp;Projects</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td data-highlight-colour=\"#fff2cc\"><p>Access&nbsp;Requests&nbsp;by&nbsp;Tech Lead</p></td><td data-highlight-colour=\"#fff2cc\"><p>Ticket Number</p></td><td data-highlight-colour=\"#fff2cc\"><p>Date Submitted</p></td><td data-highlight-colour=\"#fff2cc\"><p>SNOW Assginee</p></td><td data-highlight-colour=\"#fff2cc\"><p>Ready in Days</p></td><td data-highlight-colour=\"#fff2cc\"><p>Date Completed</p></td></tr><tr><td><p>AWS&nbsp;Work&nbsp;Space&nbsp;(VDI)</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>GitLab</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr><tr><td><p>AWS Console</p></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td><td><p /></td></tr></tbody></table><p />", "representation": "storage", "embeddedContent": [], "_expandable": { "content": "/rest/api/content/2020706439" } }, "_expandable": { "editor": "", "atlas_doc_format": "", "view": "", "export_view": "", "styled_view": "", "dynamic": "", "editor2": "", "anonymous_export_view": "" } }, "extensions": { "position": 158 }, "_expandable": { "childTypes": "", "container": "/rest/api/space/PDC", "metadata": "", "operations": "", "schedulePublishDate": "", "children": "/rest/api/content/2020706439/child", "restrictions": "/rest/api/content/2020706439/restriction/byOperation", "history": "/rest/api/content/2020706439/history", "ancestors": "", "version": "", "descendants": "/rest/api/content/2020706439/descendant", "space": "/rest/api/space/PDC" }, "_links": { "editui": "/pages/resumedraft.action?draftId=2020706439", "webui": "/spaces/PDC/pages/2020706439/Onboard+Resource+Template+for+more+than+one+resource", "context": "/wiki", "self": "https://<company>.atlassian.net/wiki/rest/api/content/2020706439", "tinyui": "/x/h4hxe", "collection": "/rest/api/content", "base": "https://pan-american.atlassian.net/wiki" } };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1"); // Please set the sheet name.
  const requests = [{ pasteData: { data: obj.body.storage.value, html: true, coordinate: { sheetId: sheet.getSheetId() } } }];
  Sheets.Spreadsheets.batchUpdate({requests}, ss.getId());

  // When you want to retrieve the values as an array, please use the following script.
  // const array = sheet.getDataRange().getValues();
  // console.log(array)
}

Result:

When this script is run, the following result is obtained.

Note:

• 此示例腳本使用您的示例值。所以當值的結構發生變化時，這個腳本可能無法使用。所以請注意這一點。
• 當我使用您的示例值時，我可以確認此腳本工作正常。因此，當您針對實際值測試此腳本時，發生錯誤時，請再次檢查檢索到的值。

參考：

• 方法：電子表格.batchUpdate
• 粘貼數據請求