Create a new snippet from a blank template.

lockUpdate (1).EXCEL.yaml

name: lockUpdate (1)
description: Create a new snippet from a blank template.
host: EXCEL
api_set: {}
script:
  content: >
    const username = "cfebj_boathouseapi";


    const listLocks = async () => {
      const res = await fetch("https://www.starpy.me/api/v1/backend/getlocklist");
      const data = await res.json();
      return data;
    };


    const setLockPassForStartEndTime = async (lockId: number, key: number,
    start: number, end: number) => {
      const res = await fetch(
        `https://www.starpy.me/api/v1/backend/setlockkey?lockId=${lockId}&key=${key}&start=${start}&end=${end}`
      );
      const data = await res.json();
      return data;
    };


    const attachCardToLock = async (lockId: number, cardId: number, start:
    number, end: number, name: string) => {
      console.info(start,end)
      const res = await fetch(
        `https://www.starpy.me/api/v1/backend/setlockcard?lockId=${lockId}&card=${cardId}&start=${start}&end=${end}&name=${name}`
      );
      const data = await res.json();
      return data;
    };


    const updateLockKeys = async (sheets: Excel.WorksheetCollection, context:
    Excel.RequestContext) => {
      // const sheets = getSheetsData();
      let sheet = sheets.getItem("LocksAndPassCodes");
      await context.sync();
      await sheet.context.sync();
      const dataRange = await sheet.getRange("A2:H100");
      await dataRange.context.load(dataRange, "values");
      await dataRange.context.sync();
      let data = dataRange.values;
      await context.sync();
      await Promise.all(
        data.map(async (lock, index) => {
          if (!lock[0] || typeof lock[0] !== "number" || isNaN(lock[0])) {
            return lock;
          }
          console.info("updating lock", lock);
          const start = lock[2].replace(new RegExp('"', "g"), "");
          const end = lock[3].replace(new RegExp('"', "g"), "");
          await setLockPassForStartEndTime(lock[0], lock[1], new Date(start).valueOf(), new Date(end).valueOf());
          return lock;
        })
      );
    };


    const updateLockList = async (sheets: Excel.WorksheetCollection, context:
    Excel.RequestContext) => {
      console.info("getlocks");
      const locks = await listLocks();
      console.info("locks", locks);
      let sheet = sheets.getItem("LockList");
      console.info("updating locklist for sheet", sheet);
      await sheet.activate();
      const range = await sheet.getRange("A1:H1000");
      await range.clear();
      await context.sync();
      let count = 1;
      sheet.getRange("A1:D1").values = [["LockId", "LockName", "README: transfer lock to " + username, "PassCodes"]];
      await context.sync();
      const lockMap = locks.map((lock) => {
        const { lockId, lockName } = lock;
        return [lockId, lockName, JSON.stringify(lock), ""];
      });
      const lockrange = "A2:D" + (locks.length + 1);
      if (locks.length) {
        const r = sheet.getRange(lockrange);
        console.info("lockupdate", lockrange, lockMap, r.toJSON());
        r.values = lockMap;
      }
    };


    $("#lockList").click(() => tryCatch(lockList));

    $("#updateLocks").click(() => tryCatch(updateKeys));

    $("#updateCards").click(() => tryCatch(updateCardList));


    async function updateCardList() {
      await Excel.run(async (context) => {
        let sheets = context.workbook.worksheets;

        console.log("Your code goes here", sheets, context);
        await updateCards(sheets, context);
        await context.sync();
      });
    }

    const updateCards = async (sheets: Excel.WorksheetCollection, context:
    Excel.RequestContext) => {
      let sheet = sheets.getItem("Cards");
      await context.sync();
      await sheet.context.sync();
      const dataRange = await sheet.getRange("A2:H100");
      await dataRange.context.load(dataRange, "values");
      await dataRange.context.sync();
      let data = dataRange.values;
      await context.sync();
      await Promise.all(
        data.map(async (lock, index) => {
          if (!lock[0] || typeof lock[0] !== "number" || isNaN(lock[0])) {
            return lock;
          }
          console.info(lock)
          const start = lock[2].replace(new RegExp('"', "g"), "");
          const end = lock[3].replace(new RegExp('"', "g"), "");
          await attachCardToLock(lock[0], lock[1], new Date(start).valueOf(), new Date(end).valueOf(), lock[4]);
          return lock;
        })
      );
    };


    async function lockList() {
      await Excel.run(async (context) => {
        let sheets = context.workbook.worksheets;

        console.log("Your code goes here", sheets, context);
        await updateLockList(sheets, context);
        await context.sync();
      });
    }


    async function updateKeys() {
      await Excel.run(async (context) => {
        let sheets = context.workbook.worksheets;

        console.log("Your code goes here", sheets, context);
        await updateLockKeys(sheets, context);
        await context.sync();
      });
    }


    /** 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="lockList" class="ms-Button">
        <span class="ms-Button-label">UPDATE LOCK LIST</span>
    </button>

    <button id="updateLocks" class="ms-Button">
        <span class="ms-Button-label">UPDATE KEYS</span>
    </button>
    <button id="updateCards" class="ms-Button">
        <span class="ms-Button-label">UPDATE CARDS</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

lockUpdate.EXCEL.yaml

name: lockUpdate
description: Create a new snippet from a blank template.
host: EXCEL
api_set: {}
script:
  content: >
    const username = "cfebj_boathouseapi";


    const listLocks = async () => {
      const res = await fetch("https://www.starpy.me/api/v1/backend/getlocklist");
      const data = await res.json();
      return data;
    };


    const setLockPassForStartEndTime = async (lockId: number, key: number,
    start: number, end: number) => {
      const res = await fetch(
        `https://www.starpy.me/api/v1/backend/setlockkey?lockId=${lockId}&key=${key}&start=${start}&end=${end}`
      );
      const data = await res.json();
      return data;
    };


    const attachCardToLock = async (lockId: number, cardId: number, start:
    number, end: number, name: string) => {
      const res = await fetch(
        `https://www.starpy.me/api/v1/backend/setlockcard?lockId=${lockId}&card=${cardId}&start=${start}&end=${end}&name=${name}`
      );
      const data = await res.json();
      return data;
    };


    const updateLockKeys = async (sheets: Excel.WorksheetCollection, context:
    Excel.RequestContext) => {
      // const sheets = getSheetsData();
      let sheet = sheets.getItem("LocksAndPassCodes");
      await context.sync();
      await sheet.context.sync();
      const dataRange = await sheet.getRange("A2:H100");
      await dataRange.context.load(dataRange, "values");
      await dataRange.context.sync();
      let data = dataRange.values;
      await context.sync();
      await Promise.all(
        data.map(async (lock, index) => {
          if (!lock[0] || typeof lock[0] !== "number" || isNaN(lock[0])) {
            return lock;
          }
          console.info("updating lock", lock);
          const start = lock[2].replace(new RegExp('"', "g"), '')
          const end = lock[3].replace(new RegExp('"', "g"), '')
          await setLockPassForStartEndTime(
            lock[0], 
            lock[1], 
            new Date(start).valueOf(), 
            new Date(end).valueOf()
          );
          return lock;
        })
      );
    };


    const updateLockList = async (sheets: Excel.WorksheetCollection, context:
    Excel.RequestContext) => {
      console.info("getlocks");
      const locks = await listLocks();
      console.info("locks", locks);
      let sheet = sheets.getItem("LockList");
      console.info("updating locklist for sheet", sheet);
      await sheet.activate();
      const range = await sheet.getRange("A1:H1000");
      await range.clear();
      await context.sync();
      let count = 1;
      sheet.getRange("A1:D1").values = [["LockId", "LockName", "README: transfer lock to " + username, "PassCodes"]];
      await context.sync();
      const lockMap = locks.map((lock) => {
        const { lockId, lockName } = lock;
        return [lockId, lockName, JSON.stringify(lock), ""];
      });
      const lockrange = "A2:D" + (locks.length + 1);
      if (locks.length) {
        const r = sheet.getRange(lockrange);
        console.info("lockupdate", lockrange, lockMap, r.toJSON());
        r.values = lockMap;
      }
    };


    $("#lockList").click(() => tryCatch(lockList));

    $("#updateLocks").click(() => tryCatch(updateKeys));

    $("#updateCards").click(() => tryCatch(updateCardList));


    async function updateCardList() {
      await Excel.run(async (context) => {
        let sheets = context.workbook.worksheets;

        console.log("Your code goes here", sheets, context);
        await updateCards(sheets, context);
        await context.sync();
      });
    }

    const updateCards = async (sheets: Excel.WorksheetCollection, context:
    Excel.RequestContext) => {
      let sheet = sheets.getItem("Cards");
      await context.sync();
      await sheet.context.sync();
      const dataRange = await sheet.getRange("A2:H100");
      await dataRange.context.load(dataRange, "values");
      await dataRange.context.sync();
      let data = dataRange.values;
      await context.sync();
      await Promise.all(
        data.map(async (lock, index) => {
          if (!lock[0] || typeof lock[0] !== "number" || isNaN(lock[0])) {
            return lock;
          }
          const start = lock[2].replace(new RegExp('"', "g"), '')
          const end = lock[3].replace(new RegExp('"', "g"), '')
          await attachCardToLock(
            lock[0], 
            lock[1], 
            new Date(start).valueOf(), 
            new Date(end).valueOf(), 
            lock[4]
          );
          return lock;
        })
      );
    };


    async function lockList() {
      await Excel.run(async (context) => {
        let sheets = context.workbook.worksheets;

        console.log("Your code goes here", sheets, context);
        await updateLockList(sheets, context);
        await context.sync();
      });
    }


    async function updateKeys() {
      await Excel.run(async (context) => {
        let sheets = context.workbook.worksheets;

        console.log("Your code goes here", sheets, context);
        await updateLockKeys(sheets, context);
        await context.sync();
      });
    }


    /** 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="lockList" class="ms-Button">
        <span class="ms-Button-label">UPDATE LOCK LIST</span>
    </button>

    <button id="updateLocks" class="ms-Button">
        <span class="ms-Button-label">UPDATE KEYS</span>
    </button>
    <button id="updateCards" class="ms-Button">
        <span class="ms-Button-label">UPDATE CARDS</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