Wrong date format while using Power Automate

Stanley Nichols
Stanley Nichols ✭✭
inAPI & Developers

I went back and forth trying to decide if I should post in a Power Automate Forum or this one so I figured I'd try here first.

I have a very simple Power Automate flow. The goal is it grabs employee name and termination date from an email being sent and insert it into a smartsheet row.

Everything works as intended minus the date, the format appears to be off. So I viewed the outlput of my variable and it is showing as:

image.png

but, smartsheet show it as:

image.png

The expression I am using to generate this is:

formatDateTime(substring(triggerBody()?['body'],add(indexOf(triggerBody()?['body'],'effective Date: '),16),10), 'M/d/yyyy')

I have verified my sheet column is a Date and I have also tried changing the date format within Smartsheet.

Any ideas would be great!

· Share on Facebook Share on Twitter

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    The format you would need to send to Smartsheet is:

    yyyy-MM-dd

    · Share on Facebook Share on Twitter
  • Stanley Nichols
    Stanley Nichols ✭✭
    The format you would need to send to Smartsheet is:<\/p>

    yyyy-MM-dd<\/p>","bodyRaw":"[{\"insert\":\"The format you would need to send to Smartsheet is:\\nyyyy-MM-dd\\n\"}]","format":"rich","dateInserted":"2023-02-22T19:12:09+00:00","insertUser":{"userID":127476,"name":"Leibel S","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Leibel%20S","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-09T00:06:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/363678#Comment_363678","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/363678#Comment_363678

    I appreciate your response!

    So I modified my expression to:

    formatDateTime(substring(triggerBody()?['body'],add(indexOf(triggerBody()?['body'],'effective Date: '),16),10), 'yyyy-MM-dd')

    To go with the format you suggested. Output of Power Automate is:

    image.png

    However, the inserted row is still the same:

    image.png

    Is there anything else you can think of I am doing wrong?

    · Share on Facebook Share on Twitter
  • Stanley Nichols
    Stanley Nichols ✭✭

    After reading up on Smartsheets API documentation I have figured it out. According to Smartsheet:

    The Smartsheet API returns all dates and times in theUTCtime zone inISO-8601format, that is, YYYY-MM-DDTHH:MM:SSZ. If you are specifying a date and time, you should also send that information in ISO-8601 format. If a date/time needs to be displayed to an end-user in their local time zone, you must do the conversion using the user's time zone, which you can obtain bygetting the current user.

    So, changing my compose expression to:

    formatDateTime(substring(triggerBody()?['body'],add(indexOf(triggerBody()?['body'],'effective Date: '),16),10), 'yyyy-MM-ddTHH:mm:sszzz', 'UTC')

    Then had this as an output:

    image.png


    I also modified my Set Date Variable within Power Automate to this:

    trim(substring(triggerBody()?['body'],add(indexOf(triggerBody()?['body'],'Effective Date: '),16),10))

    Which outputs our end result:

    image.png

    I appreciate the help and I apologize I should have viewed the API docs more in depth prior.

    Thanks!

    · Share on Facebook Share on Twitter

Categories

Feel free to submit a Product Idea!<\/p>

There's nothing we can do as users. It would require a rebuild of that system. I'm pretty sure it's resource intensive, and runs on a schedule performing the automations.<\/p>"},{"commentID":388346,"body":"

No problem. Sorry I didn't have a way to help solve your problem. <\/p>

Having to wait is especially frustrating for me when I'm giving a demo, but that's not the only time I'd like them to execute faster. <\/p>

Consider submitting a Product Idea here (if a search for an already made suggestion isn't available for you to upvote. <\/p>

\n \n https:\/\/community.smartsheet.com\/post\/idea\n <\/a>\n<\/div>

All the best,<\/p>

-Ray<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107839,"type":"question","name":"Extract List of Unique Values","excerpt":"Hi Is it possible to use Bridge to extract from a column a list of values, run a JS to extract an array of unique values, and then pass this to another sheet and update another column with this unique list?","snippet":"Hi Is it possible to use Bridge to extract from a column a list of values, run a JS to extract an array of unique values, and then pass this to another sheet and update another…","categoryID":320,"dateInserted":"2023-07-20T06:57:54+00:00","dateUpdated":"2023-07-20T07:09:25+00:00","dateLastComment":"2023-07-23T23:46:17+00:00","insertUserID":125212,"insertUser":{"userID":125212,"name":"Neil Watson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Neil%20Watson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pglbKXXltro!HIFbX6W_ivo!Lnqe5-nvNY5","dateLastActive":"2023-08-11T03:27:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":125212,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:25:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":134,"score":null,"hot":3379997051,"url":"https:\/\/community.smartsheet.com\/discussion\/107839\/extract-list-of-unique-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107839\/extract-list-of-unique-values","format":"Rich","tagIDs":[369],"lastPost":{"discussionID":107839,"commentID":386578,"name":"Re: Extract List of Unique Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386578#Comment_386578","dateInserted":"2023-07-23T23:46:17+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:25:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-23T08:22:10+00:00","dateAnswered":"2023-07-21T12:06:06+00:00","acceptedAnswers":[{"commentID":386363,"body":"

@Neil Watson<\/a> You should be able to use the LOWER function for evaluation but still output the version that includes caps.<\/p>

=IFERROR(INDEX(DISTINCT(LOWER(<\/strong>COLLECT(.....................))<\/strong>), 1), \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>


<\/p>

I haven't tested it yet, but I feel like it should work.<\/p>"},{"commentID":386435,"body":"

Right. There are already a number of posts here in the community detailing how to pull a distinct or unique list from another sheet via formula. It usually involves the formula above without the LOWER function. The number 1 would output the first distinct value. Changing that to a 2 will output the second distinct value, so on and so forth. I usually use a helper column with the numbers manually entered so I can apply it as a column formula.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":369,"urlcode":"bridge-by-smartsheet","name":"Bridge"}]},{"discussionID":107219,"type":"question","name":"How to use access_token? (api.smartsheet.com\/2.0\/token)","excerpt":"Node.js How to use access_token? I'm making a request: axios.post('https:\/\/api.smartsheet.com\/2.0\/token')... response: { access_token: 'xxx', token_type: 'bearer', refresh_token: 'xxx', expires_in: 604799 } var smartsheet = client.createClient({ accessToken: 'xxx', logLevel: 'info', }); But the result is always the same:…","snippet":"Node.js How to use access_token? I'm making a request: axios.post('https:\/\/api.smartsheet.com\/2.0\/token')... response: { access_token: 'xxx', token_type: 'bearer', refresh_token:…","categoryID":320,"dateInserted":"2023-07-05T05:43:29+00:00","dateUpdated":"2023-07-05T09:15:26+00:00","dateLastComment":"2023-08-07T12:06:57+00:00","insertUserID":163125,"insertUser":{"userID":163125,"name":"Bortyk","url":"https:\/\/community.smartsheet.com\/profile\/Bortyk","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-09T05:34:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-11T09:36:17+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":135,"score":null,"hot":3379948226,"url":"https:\/\/community.smartsheet.com\/discussion\/107219\/how-to-use-access-token-api-smartsheet-com-2-0-token","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107219\/how-to-use-access-token-api-smartsheet-com-2-0-token","format":"Rich","lastPost":{"discussionID":107219,"commentID":389163,"name":"Re: How to use access_token? (api.smartsheet.com\/2.0\/token)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389163#Comment_389163","dateInserted":"2023-08-07T12:06:57+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-11T09:36:17+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-09T01:57:51+00:00","dateAnswered":"2023-08-07T12:06:57+00:00","acceptedAnswers":[{"commentID":389163,"body":"

Hey @Bortyk<\/a> <\/p>

An Access Token will only provide you with the same permissions and access that the account has in the Smartsheet UI. <\/p>

For example, if you're trying to Edit a sheet through the API, you'll need to have at least Editor permissions on that sheet in the UI. <\/p>

I would suggest making sure that the account you're using to access the API has the correct permissions on each item and also in the account as well (e.g. if it needs to be a System Admin for the plan or not).<\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=320&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":209,"limit":3},"title":"Trending in API & Developers","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in API & Developers

  • Is there any way to get the email faster?

    Email Trigger takes 3-5 mins. I need to get it faster. Is there any possible to get faster?. can you please update this issue as soon as possible?

  • 提取的惟一值列表

    Hi Is it possible to use Bridge to extract from a column a list of values, run a JS to extract an array of unique values, and then pass this to another sheet and update another column with this unique list?

  • How to use access_token? (api.smartsheet.com/2.0/token)

    Node.js How to use access_token? I'm making a request: axios.post('https://api.smartsheet.com/2.0/token')... response: { access_token: 'xxx', token_type: 'bearer', refresh_token: 'xxx', expires_in: 604799 } var smartsheet = client.createClient({ accessToken: 'xxx', logLevel: 'info', }); But the result is always the same:…