Create a new snippet from a blank template.

Summit2023Template.EXCEL.yaml

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

    const sourceSheetName = "Fill this sheet";
    const resultSheetName = "Export";

    const summitPrefix = "emea-event-agenda";
    let summitName = "";
    const language = "German";

    const columns = {
      sessionId: 0,
      title: 1,
      abstract: 2,
      speakers: 3,
      track:4,
      room:5,
      floor: 6,
      time: 7,
      duration: 8,
      level: 9,
      language: 10,
      sessionType: 11,
      role: [12, 13, 14],
      segment: [15,16,17],
      industry: [18,19,20],
      category: [21, 22, 23, 24, 25],
    };

    const tables = {
      "role": {
        "Business Decision Maker":"business-decision-maker",
        "Business Professional":'business-manager',
        "IT Manager":"it-manager",
        "Technical Decision Maker":"technical-decision-maker",
        "Architect":"architect",
        "Data Analyst":"data-analyst",
        "Data Scientist":"data-scientist",
        "Developer":"developer",
        "Startup Founder":"founder",
        "Infosec":"infosec",
        "IT Administrator":"it-administrator"
      },
      "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",
      },
      "segment": {
        "Enterprise":"enterprise",
        "Digital Native Business":"digital-native",
        "Small & Medium Business":"smb",
        "Independent Software Vendor":"isv",
        "Startup":"startup",
        "Public Sector":"public-sector",
        "Non Profit":"non-profit",
      },
      "industry": {
        "Agriculture":"agriculture",
        "Automotive": "automotive",
        "Computer & Electronics": "electronics",
        "Consumer Goods": "cpg",
        "Education": "education",
        "Financial Services": "financial-services",
        "Gaming": "gaming",
        "Government": "general-public-services",
        "Healthcare": "healthcare",
        "Hospitality": "hospitality",
        "Life Sciences": "life-sciences",
        "Manufacturing": "manufacturing",
        "Marketing & Advertising": "digital-marketing",
        "Media & Entertainment": "media-entertainment",
        "Mining": "mining-natural-resource",
        "Non - Profit Organization": "non-profit",
        "Oil & Gas": "oil-gas",
        "Power & Utilities": "power-utilities",
        "Professional Services": "prof-services",
        "Real Estate & Contruction": "construction-real-estate",
        "Retail": "retail",
        "Software & Internet": "software-internet",
        "Telecommunications": "telecommunications",
        "Transportation & Logistics": "transport-logistics",
        "Travel & Leisure": "travel"
      }
    };

    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 = [];
        summitName = values[0][1];
        for (var i = 5; i < values.length; i++) {
          const session = values[i];

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

        const result = [];
        for (let session of sessions) {
          const data = [
            session.sessionId,
            session.itemNameId,
            session.itemTags,
            session.version,
            session.abstract,
            session.deepLinkUrl,
            session.duration,
            session.enableShare,
            session.floor,
            session.language,
            session.level,
            session.room,
            session.speakers,
            session.time,
            session.title,
            session.track
          ];
          result.push(data);
        }
        console.log(result);
        
        // 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:P${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 = {
        sessionId: `${summitPrefix}#${summitName}-${session[columns.sessionId]}`,
        itemNameId: `${summitName}-${session[columns.sessionId]}`,
        itemTags: generateItemTags(session),
        version: "",
        abstract: session[columns.abstract],
        deepLinkUrl: generateDeepLink(session),
        duration: session[columns.duration],
        enableShare: 1,
        floor: session[columns.floor],
        language: session[columns.language],
        level: session[columns.level],
        room: session[columns.room],
        speakers: session[columns.speakers],
        time: session[columns.time],
        title: session[columns.title],
        track: session[columns.track]
      };

      return obj;
    }

    function generateDeepLink(session) {
      let link = `https://aws.amazon.com/fr/events/summits/paris/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 = `emea-event-agenda#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("Level ", "")}`;
      }

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

      for (let element of ["role", "category", "segment", "industry"]) {
        console.log(`element: ${element}`);
        for(let i of columns[element]) {
          console.log(`\tvalue: ${session[i]}`);
          if (session[i] !== "") {
            const value = tables[element][session[i]];
            if (value) {
              tag += `,GLOBAL#${element}#${value}`;
            }
          }
        }
      }

      return tag;
    }

    /** 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