Obsah Soutoku stránku s Apps Script a analyzovat jeho obsah

Věřím, že vaším úkolem je následující.

• Budete chtít analyzovat HTML ze vzorku hodnota (it generates the content, but it is difficult to parse:) na vaši otázku a dát je na plech.
  • Už jste byli schopni získat hodnotu z rozhraní API, které chcete použít.

V tomto případě, jak se o následující ukázkový skript? V tomto případě, chtěl bych navrhnout pomocí Listů API. Na pasteData Listů API může analyzovat HTML tabulky.

Ukázkový skript:

Prosím, zkopírujte a vložte následující skript do editoru skriptu z Tabulky Google. A tento skript používá Listy API. Takže prosím, povolte Listy API na Pokročilé služby Google. A, prosím, nastavit název listu. A spustit funkci. Tím, Povlečení API analyzuje HTML tabulka a vloží do listu.

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

Výsledek:

Když tento skript je spuštěn následující výsledek je získán.

Poznámka:

• Tento ukázkový skript používá vaše hodnota vzorku. Takže když struktura je změněna hodnota, tento skript nemusí být možné použít. Takže prosím, buďte opatrní.
• Mohu potvrdit, že tento skript funguje v pohodě, když jsem použít své hodnoty vzorku. Takže, když jste testovali tento skript pro vaše skutečné hodnoty, pokud dojde k chybě, prosím, zkontrolujte načtené hodnoty znovu.

Odkazy:

• Metoda: tabulky.batchUpdate
• PasteDataRequest

Zde je možné řešení pomocí regulární výrazy extrahovat informace z createResponse.getContentText(). To je méně obecný než řešení @Tanaike dispozici, ale nevyžaduje, aby generovat auxiliar karta (list) na aktivní Tabulky, které musí být odstraněny po získání všech záznamů:

/*
@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;
}

Já jsem rozebrat konkrétní hodnoty pro každý řádek, protože formát Soutoku má na můj konkrétní palubní Soutoku Šablony.

Vyvolání funkce se specifickými Soutoku ID:

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

vytvoří následující výstup pro onboarding šablony s dvou zdrojů:

[["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]"]]]