Obține Conținut de o Confluență pagina cu Apps Script și analiza conținutul său

Cred ca scopul este după cum urmează.

• Doriți să parse HTML din eșantion valoare (it generates the content, but it is difficult to parse:) în cauză și le-a pus la foaie.
  • Ai fost deja în măsură pentru a prelua valoarea din API doriți să o utilizați.

În acest caz, cât despre următorul exemplu de script? În acest caz, aș dori să propun, folosind Foi de API. La pasteData de Foi API poate parse HTML table.

Script eșantion:

Vă rugăm să copiați și lipiți următorul script la script editor de foi de Calcul Google. Și, acest script utilizează Foi de API. Deci, vă rugăm să activați Foi API Avansat la serviciile Google. Și, vă rugăm să setați numele foii. Și executați funcția. Prin aceasta, Foi API analizează tabel HTML și pune la foaie.

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

Rezultatul:

Atunci când acest script este rulat, se obține următorul rezultat.

Notă:

• Acest script eșantion utilizează proba ta valoare. Deci, atunci când structura de valoare este schimbat, acest scenariu ar putea să nu fi capabil de a fi folosit. Deci, vă rog, fi atent.
• Pot confirma că acest script funcționează bine atunci când folosesc de proba ta valoare. Deci, atunci când l-ai testat acest script pentru valoarea reală, atunci când apare o eroare, vă rugăm să verificați preluate valoarea din nou.

Referințe:

• Metoda: foi de calcul.batchUpdate
• PasteDataRequest

Aici o posibilă soluție folosind expresii regulate pentru a extrage informații de la createResponse.getContentText(). Este mai generic decât soluția @Tanaike furnizate, dar ea nu are nevoie pentru a genera un auxiliar tab (foaie) pe activ foaie de Calcul care trebuie să fie eliminate după ce toate înregistrările:

/*
@param id {String} Confluence ID page 
*/
function getOnboardRecords(id) {
  const URL = https://<COMPANY>.atlassian.net/wiki/rest/api/content/%s?expand=body.storage;
  const TOKEN = "Provide the token";
  const USER = "Provide user name, i.e an email";
  // Relevant columns to get the information
  const ROW_NAMES = ["First Name", "Last Name", "Phone Number", 
    "E-Mail", "Role", "Start Date", "End Date", "Vendor's Name",
    "Team", "New Resource ID","New Resource Company E-mail"];

  function parsePhone(s) {
    let ss = s.replace(/[ \(\)\-+]/g, "");
    return (ss.length == 10) ? "1" + ss : ss; // Adding US code
  }

  function parseDate(d) {
    let dateRegEx = new RegExp(/<time datetime=\\"([0-9]{4}-[0-9]{2}-[0-9]{2})\\" ?\/>/);
    return d.match(dateRegEx)[1];
  }

  function parseEmail(e) {
    let s = e.replaceAll(/<.*?>|<\/.*?>/g, "");
    let validEmailRegEx = new RegExp(/^\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*/);
    const NON_VALID_EMAIL = "The input argument: '%s' is not a valid email";
    if (!validEmailRegEx.test(s)) throw new Error(Utilities.formatString(NON_VALID_EMAIL, result));
    return s;
  }


  let headers = { "Authorization": "Basic " + Utilities.base64Encode(USER + ':' + TOKEN) };
  let params = {
    "method": "GET",
    "headers": headers,
    "muteHttpExceptions": false,
    "contentType": "application/json"
  };

  let url = Utilities.formatString(URL, id);
  let createResponse = UrlFetchApp.fetch(url, params);
  let content = createResponse.getContentText();
  let tableRegEx = new RegExp("<table.*?>(.*?)</table>");
  let tableHtml = content.match(tableRegEx)[0];
  let rowRegEx = new RegExp("<tr.*?>(.*?)</tr>", "g");
  let rowsResult = tableHtml.match(rowRegEx);
  let colRegExp = new RegExp("<td.*?>(.*?)</td>", "g");
  let values = new Map();
  rowsResult.forEach(function (item) { // parsing each column of given row
    item = item.replace(/<p>|<\/p>|<p ?\/>/g, "").replace(/&nbsp;/g, " ");
    let row = [];
    [...item.matchAll(colRegExp)].forEach(item => row.push(item[1])); // Getting the group (.*?)
    row = row.filter(function (item) { return item != ""; }); // Keep non empty string only
    let key = ROW_NAMES.find(it => it == row[0]);
    if (key) {
      if (row.length > 1) {
        let records = row.slice(1);
        if (key == ROW_NAMES[2]) { // phone number
          records.forEach(function (item, index) {
            this[index] = parsePhone(item);
          }, records);
        }
        if ((key == ROW_NAMES[5]) || (key == ROW_NAMES[6])) {
          records.forEach(function (item, index) {
            this[index] = parseDate(item);
          }, records);
        }
        if ((key == ROW_NAMES[3]) || (key == ROW_NAMES[10])) {
          records.forEach(function (item, index) {
            this[index] = parseEmail(item);
          }, records);
        }
        values.set(key, records);
      }
    }

  });
  return values;
}

Sunt parsarea valori specifice pentru fiecare rând, pentru că a format de Confluența are asupra mea specifice la bordul Confluența Șablon.

Invocarea funcției cu anumite Confluența ID:

let onboardValues = getOnboardRecords(<ID>);
console.log(JSON.stringify([...onboardValues.entries()]))

va produce următoarea ieșire pentru un onboarding șablon cu două resurse:

[["First Name",["FirstName1","FirstName2"]],["Last Name",
["LastName1","LastName2"]],["Phone Number",["xxxxxxxxx","xxxxxxxxx"]],
["E-Mail",["[email protected]","[email protected]"]],["Role",
["Delivery Manager","Sr. Developer"]],["Start Date",
["2021-11-01","2021-11-01"]],["End Date",["2021-11-30","2021-12-31"]],
["Vendor's Name",["xxxx","xxxxx"]],["Team",["xxxxxx]],
["New Resource ID",["xxxxxx","xxxxxx"]],
["New Resource Company E-mail",["[email protected]","[email protected]"]]]