Create a new snippet from a blank template.

Paris Summit.EXCEL.yaml

name: Paris Summit
description: Create a new snippet from a blank template.
host: EXCEL
api_set: {}
script:
  content: >
    //$("#run").click(() => tryCatch(run));

    tryCatch(run);


    const sourceSheetName = "Progress Tracking";

    const resultSheetName = "Export";


    const summitPrefix = "emea-event-agenda";

    const summitName = "summit-paris-2023";

    const city = "paris";

    const language = "french";


    const columns = {
      sessionId: 0,
      title: 5,
      abstract: 6,
      awsSpeakers: 42,
      customerSpeakers: 15,
      sessionType: 38,
      room: 39,
      floor: 40,
      time: 41,
      level: 7,
      role: [8, 9, 10],
      "tech-category": [11, 12, 13],
      fakeSessionId: 43
    };


    const tracksList = [
      {
        label: "SUSTAINABILITY",
        sessions: ["SUS102", "SUS204", "SUS101", "AIM204", "SUS103"]
      },
      {
        label: "INCLUSION, DIVERSITÉ & EQUITÉ",
        sessions: ["AIM206", "AIM311", "IDE101"]
      }
    ];


    const header1 = ["DIRECTORY", "emea-event-agenda", "LOCALE", "fr_FR",
    "FORMAT_VERSION", "1.1.0", "TIMESTAMP", "2023-02-13T13:39:34+00:00",
    "REQUESTER", "tobsch", "ITEM_STATUS", "PUBLISHED", "CONTENT", "ITEMS", "",
    "", ""];

    const header2 = ["item_id", "item_name", "item_tags", "version", "abstract",
    "deep-link-url", "duration", "enableShare", "floor", "language", "level",
    "room", "session-id", "speaker", "time", "title", "track"];


    const durationsList = {
      "breakout-session": "45 minutes",
      "workshop": "105 minutes",
      "sponsor-session": "45 minutes",
      "startup-session": "30 minutes",
      "community-sessions": "30 minutes"
    }


    const tables = {
      role: {
        "Business Decision Maker": "business-decision-maker",
        "IT Manager": "it-manager",
        "Technical Decision Maker": "technical-decision-maker",
        Architect: "architect",
        "Data Engineer": "data-analyst",
        "Data Scientist": "data-scientist",
        Developer: "developer",
        "Info Sec": "infosec",
        "IT Pro / Admin": "it-administrator",
      },
      "tech-category": {
        Analytics: "analytics",
        "Application Integration": "app-integration",
        "AR & VR": "ar-vr",
        "Architecture Strategy": "arch-strategy",
        Blockchain: "blockchain",
        "Business Applications": "business-apps",
        "Cloud Financial Management": "cost-mgmt",
        "Cloud Foundations": "cloud-foundations",
        Compute: "compute",
        "Contact Center": "contact-center",
        Containers: "containers",
        Databases: "databases",
        "Developer Tools": "devtools",
        "End-User Computing(EUC)": "euc",
        "Front-End Web & Mobile": "mobile",
        "Game Tech": "game-tech",
        IoT: "iot",
        "Machine Learning & AI": "ai-ml",
        "Management & Governance": "mgmt-govern",
        "Media Services": "media-services",
        Migration: "migration",
        "Modern Applications": "modern-applications",
        "Networking & Content Delivery": "networking-content-dev",
        "Quantum Technologies": "quantum",
        Robotics: "robotics",
        Satellite: "satellite",
        "Security, Identity, Compliance": "security-identity-compliance",
        Serverless: "serverless",
        Storage: "storage"
      },
    };


    async function run() {
      await Excel.run(async (context) => {
        //const sheet = context.workbook.worksheets.getActiveWorksheet();
        const sheet = context.workbook.worksheets.getItemOrNullObject(sourceSheetName);
        const range = sheet.getUsedRange();
        sheet.load(["name", "items", "values", "range", "rowCount"]);
        range.load(["values", "rowCount"]);
        await context.sync();

        console.log(`name : ${range.rowCount}`);
        console.log(range.values);
        const nbRows = range.rowCount;
        const values = range.values;

        const sessions = [];
        for (var i = 5; i < values.length; i++) {
          const session = values[i];

          const obj = buildObject(session);
          sessions.push(obj);
        }

        const result = [];
        result.push(header1);
        result.push(header2);
        for (let session of sessions) {
          const data = [
            session.itemId,
            session.itemName,
            session.itemTags,
            session.version,
            session.abstract,
            session.deepLinkUrl,
            session.duration,
            session.enableShare,
            session.floor,
            session.language,
            session.level.replace("L-", "Niveau "),
            session.room,
            session.fakeSessionId,
            session.speakers,
            session.time,
            session.title,
            session.track
          ];
          result.push(data);
        }

        // Delete and recreate sheet
        context.workbook.worksheets.getItemOrNullObject(resultSheetName).delete();
        context.workbook.worksheets.add(resultSheetName);
        await context.sync();

        const newrange = context.workbook.worksheets.getItemOrNullObject(resultSheetName).getRange(`A1:Q${result.length}`);

        newrange.load("values");
        await context.sync();
        newrange.values = result;

        //addData(context, result);
        $("#result").text(`Done. You can find the result in the '${resultSheetName}' tab`);
      });
    }


    async function addData(context, rows) { }


    function buildObject(session) {
      const obj = {
        itemId: `${summitPrefix}#${summitName}-${session[columns.sessionId].toLowerCase()}`,
        itemName: `${summitName}-${session[columns.sessionId]}`,
        itemTags: generateItemTags(session),
        version: "",
        abstract: session[columns.abstract],
        deepLinkUrl: generateDeepLink(session),
        duration: generateDuration(session),
        enableShare: 1,
        floor: session[columns.floor],
        language: "French",
        level: session[columns.level],
        room: session[columns.room],
        sessionId: session[columns.sessionId],
        fakeSessionId: generateFakeSessionId(session),
        speakers: generateSpeakers(session),
        time: session[columns.time],
        title: session[columns.title],
        track: generateTrack(session)
      };

      return obj;
    }


    function generateDeepLink(session) {
      let link = `https://aws.amazon.com/fr/events/summits/${city}/agenda/?emea-event-agenda-card.sort-by=item.additionalFields.title&emea-event-agenda-card.sort-order=asc&awsf.emea-event-agenda-level=*all&awsf.emea-event-agenda-role=*all&awsf.emea-event-agenda-category=*all&awsf.emea-event-agenda-aws-industry=*all&emea-event-agenda-card.q=${
        session[columns.sessionId]
        }&emea-event-agenda-card.q_operator=AND#Catalogue_des_sessions`;

      return link;
    }


    function generateItemTags(session) {
      let tag = `${summitPrefix}#event-name#${summitName},emea-event-agenda`;

      // Add session type
      tag += `#session-type#${session[columns.sessionType]}`;

      // Add level
      if (session[columns.level]) {
        tag += `,GLOBAL#level#${session[columns.level].replace("L-", "")}`;
      }

      // Add language
      tag += `,GLOBAL#language#${language}`;

      for (let element of ["role", "tech-category"]) {
        for (let i of columns[element]) {
          if (session[i] !== "") {
            const value = tables[element][session[i]];
            if (value) {
              tag += `,GLOBAL#${element}#${value}`;
            }
          }
        }
      }

      return tag;
    }


    function generateSpeakers(session) {
      let speakers = session[columns.awsSpeakers];
      if ((session[columns.customerSpeakers] !== "") && (session[columns.customerSpeakers] !== "NA") && (session[columns.customerSpeakers] !== "TBD")) {
        speakers += ` & ${session[columns.customerSpeakers]}`;
      }
      return speakers;
    }


    function generateDuration(session) {
      return durationsList[session[columns.sessionType]];
    }


    function generateTrack(session) {
      for(let track of tracksList) {
        if (arrayContains(track.sessions, session[columns.sessionId])) {
          return track.label;
        }
      }
      return "";
    }


    function generateFakeSessionId(session) {
      if (session[columns.fakeSessionId] !== "") {
        console.log(`Fake session id ${session[columns.fakeSessionId]} found for session ${session[columns.sessionId]}`);
        return session[columns.fakeSessionId];
      }
      return session[columns.sessionId];
    }


    function arrayContains (array, element) {
      return array.indexOf(element) >= 0;
    };

    /** Default helper for invoking an action and handling errors. */

    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }
  language: typescript
template:
  content: |
    <button id="run" class="ms-Button">
        <span class="ms-Button-label">Run</span>
    </button>
  language: html
style:
  content: |-
    section.samples {
        margin-top: 20px;
    }

    section.samples .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

  core-js@2.4.1/client/core.min.js
  @types/core-js

  jquery@3.1.1
  @types/jquery@3.3.1