cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bill51106
Helper IV
Helper IV

JSON issues using MSGRAPH for Excel

Hello everyone!

 

I have been using the following guide for reference LINK 

How do I get my Excel file into JSON correctly? I used the following script:

 

function main(workbook: ExcelScript.Workbook): TableData[] {
    // Get the first table in the "PlainTable" worksheet.
    // If you know the table name, use `workbook.getTable('TableName')` instead.
    const table = workbook.getTable('Table1');

    // Get all the values from the table as text.
    const texts = table.getRange().getTexts();

    // Create an array of JSON objects that match the row structure.
    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.
  // In this case, we have defined the TableData object, but any similar interface would work.
  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 {
   
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
    "X": string
  }
}

 

 

But the Flow fails with a 413 error.

Capture.PNG

 

5 REPLIES 5
grantjenkins
Community Champion
Community Champion

What's the trigger for your flow? And what's the original Excel data/payload you are working with before running the Office Script?

 

If you're following the instructions from the site you linked to, then not sure why you're using Office Scripts at all. Are you wanting to use the Graph API to add your rows as per the instructions on the site?



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
Bill51106
Helper IV
Helper IV

@grantjenkins I don't know how to get my data into JSON without office scripts?

Where is your data coming from initially? Just wondering what your initial data looks like prior to trying to get it into JSON format.



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
grantjenkins
Community Champion
Community Champion

Are you initially wanting to get your data from an Excel Table? If so, then you should be using List rows present in a table that will retrieve all the records and will already be in JSON format.

 

grantjenkins_0-1669952198981.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
grantjenkins
Community Champion
Community Champion

See your other post for details.

 

Re: JSON array object error - Power Platform Community (microsoft.com)



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (1,776)