Dave
Dave

Reputation: 1

Getting data for JSON from tables across all visible sheets in a workbook

I have an Excel Online workbook where each sheet represents an active job. Inactive jobs (sheets) are hidden on the same workbook. I'm trying to create script that will cycle through all visible sheets and get data from a persistent table within the workbook and log the table data to JSON. My aim to have this JSON data import to an email that is set on schedule to send every 24hours.

The persistent table across each worksheet (representing a job) has three column headings: "Date", "Action", and "User".

Its a bit of a Frankenstein's monster at the moment due to my extremely limited knowledge, but here's what I have so far:

  const sheets = workbook.getWorksheets();
  let sheetVis: ExcelScript.Worksheet[] = [];
  sheets.forEach(sheet => {
    if (sheet.getVisibility() === ExcelScript.SheetVisibility.visible) {
      const table = sheet.getTables()[0];
      const texts = table.getRange().getTexts();
        let returnObjects: TableData[] = [];
        if (table.getRowCount() > 0) {
          returnObjects = returnObjectFromValues(texts);
          }
        // Log the information and return it for a Power Automate flow.
        console.log(JSON.stringify(returnObjects));
        return returnObjects
        }
      // This function converts a 2D array of values into a generic JSON object.
      function returnObjectFromValues(values: string[][]): TableData[] {
        let objectArray: TableData[] = [];
        let objectKeys: string[] = [];
        for (let i = 0; i < values.length; i++) {
          if (i === 0) {
          objectKeys = values[i]
          continue;
          }
        let object: {[key: string]: string} = {}
        for (let j = 0; j < values[i].length; j++) {
          object[objectKeys[j]] = values[i][j]
          }
        objectArray.push(object as unknown as TableData);
        }
      return objectArray;
      }
    interface TableData {
    DATE: string
    ACTION: string
    USER: string
      }
    sheetVis.push(sheet); 
    }
  ); 
}```

Currently, I'm encountering an error on line 1 with TableData

Upvotes: 0

Views: 26

Answers (0)

Related Questions