Answered

Excel Data to Sharepoint List - Date/All Day Event

Like (0) Share

Report

Posted on 29 Apr 2021 19:20:25 by Community Power Pla...

Hi all,

I have spent a ridiculous amount of time scrolling through many many Q&A's here trying to figure out this very simple process, but I've now butchered it with so many changes trying to fix it that I am totally lost.

Trying to pull Excel data (birthdays, anniversaries) into a Sharepoint list to add to a calendar. I've given up trying to schedule this for new rows/data, now I'll just be happy to have the current data imported and I'll update new items manually.

Wanting calendar entries to be all-day and recurring year over year.

Excel pull is working, I believe the issue is with my date formatting. Error is "Cannot convert a primitive value to the expected type 'Edm.DateTime'." Not sure if it's stemming from the variable actions I have, or happening solely at the item creation level.

As much detail as you can provide is great, I am a newbie : ) TIA

All responses (2)

Answers (1)

Community Power Pla... on 06 May 2021 at 15:55:54

Like (0)

Report

Re: Excel Data to Sharepoint List - Date/All Day Event

Hi Anna!

Thanks for your help, I am still getting an error when trying to run. Do I need to remove my variable set and instead create HTML table? If so, will the formatting of the HTTP request change?

Here is the setup and error:
{
    "statusCode": 400,
    "headers": {
        "Pragma": "no-cache",
        "x-ms-request-id": "9f48c59f-006d-0000-c877-345736844c1c",
        "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
        "X-Content-Type-Options": "nosniff",
        "X-Frame-Options": "DENY",
        "Cache-Control": "no-store, no-cache",
        "Set-Cookie": "ARRAffinity=4ec824438a5db7c43d418f83dbbfe9a3ba20b7e8f1759a439053c598aad95139;Path=/;HttpOnly;Secure;Domain=sharepointonline-ncus.azconn-ncus.p.azurewebsites.net,ARRAffinitySameSite=4ec824438a5db7c43d418f83dbbfe9a3ba20b7e8f1759a439053c598aad95139;Path=/;HttpOnly;SameSite=None;Secure;Domain=sharepointonline-ncus.azconn-ncus.p.azurewebsites.net",
        "Timing-Allow-Origin": "*",
        "x-ms-apihub-cached-response": "false",
        "Date": "Thu, 06 May 2021 15:47:49 GMT",
        "Content-Length": "502",
        "Content-Type": "application/json",
        "Expires": "-1"
    },
    "body": {
        "status": 400,
        "message": "Cannot convert a primitive value to the expected type 'Edm.DateTime'. See the inner exception for more details.\r\nclientRequestId: e2d52dd7-afa1-41fe-b8bf-b0aac324ab73\r\nserviceRequestId: 9f48c59f-006d-0000-c877-345736844c1c",
        "source": "https://xxxxxxxxxxxxxxx.sharepoint.com/sites/HumanResources/_api/web/lists/getbytitle('Employee%20Celebrations')/items",
        "errors": [
            "-1",
            "Microsoft.SharePoint.Client.InvalidClientQueryException"
        ]
    }
}
Verified answer

v-duann-msft on 30 Apr 2021 at 02:48:05

Like (0)

Report

Re: Excel Data to Sharepoint List - Date/All Day Event

Hi @Anonymous

Thank you for posting.

Yes, you are right. The main issue is caused by date format comes from excel file.

The date output from excel should be a number like 44317 instead of 2021-05-01 even though they are formatted as date in excel.

So, if you want date exported from excel look like a date, we must do conversion first using below expression (The bolded part is the dynamic content of that date column).

formatDateTime(addDays('1899-12-31',int(items('Apply_to_each)?['HireDate'])),'yyyy-MM-dd')

Hope the content above may help you.

Best regards,
Anna

Community site session details

Excel Data to Sharepoint List - Date/All Day Event

Helpful resources

News and Announcements

Quick Links

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Announcing the Engage with the Community forum!

Leaderboard > Power Automate

Featured topics

Product updates

Community site session details

Excel Data to Sharepoint List - Date/All Day Event

Helpful resources

News and Announcements

Quick Links

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Announcing the Engage with the Community forum!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate

Featured topics

Product updates