How can I add conversation to a specific row id in Power Automate using excel as connector?
Hello,
I am currently working on a flow in Power Automate that connects with Excel to update specific rows in Smartsheet. The goal is to add a discussion to a row in Smartsheet by adding an email as a comment to a specific row id.
However, when I filter for the row id, the output is displayed in number format with commas. Smartsheet expects the row id as a number without commas, and I suspect this formatting discrepancy is preventing the correct match between the filtered row id and the row id in smartsheet.
I have been using int(replace(item()?['RowID'], ',', '')) or int(replace(string(item()?['RowID'])), ',', '')) functions in the compose action in Power Automate to remove commas keeping the number format. Despite this, I still see the row id with commas in the output 5,269,399,577,563,010.
Has anyone faced the same issue? Any advice on how to match formats to get the row id recognized while adding a discussion to a row in Smartsheet ( row id 5,269,399,577,563,010 vs row id 5269399577563010)
Best Answer
-
In my flow, the "List rows present in a table" action precedes the "For each."
I can not see your "For each" item, but you can get each row of the table with an expression like this;
outputs('List_rows_present_in_a_table')?['body/value']
Then, you can specify the parameters of the "add a discussion to a row" action, using the current for each item.
items()?['Comment']
Answers
-
I encountered an issue working with Smartsheet Row IDs in Excel and Power Automate. The problem stemmed from Excelโs number formatย cutting off information from the Smartsheet Row IDย due to its limitations in accurately handling very large numbers. Since Smartsheet Row IDs are extremely long integers, Excel converted them to scientific notation or truncated some digits, making using them as Row Id input impossible.
To address this issue, I stored the Row IDs as textย in Excel instead of using the default number format. I could preserve their accuracy by storing them as text without truncation or conversion to scientific notation. This approach wasย successful, and I could see the Row IDs displayed precisely as they were in Smartsheet, ensuring that no information was lost.
Initially, I attempted to convert the Row ID from number format to a string directly in Excel. (=TEXT(D8, "0") as shown in the above image.) However, this approach failed because the information had already been cut off when Excel initially treated the IDs as numbers. Once Excel truncated or altered the Row IDs due to its number format limitations, it was impossible to recover the original information simply by converting them to strings. The damage was done at the moment Excel interpreted them as numbers.
As you can see, the first row has Row ID 5599319910518660. The Row ID Text converted to text with the formula was the same, as the last digit was 0. So, I could only add a comment to this row. Then I realized Excel's truncation in the number format.
To work around this issue effectively in Power Automate, I continued storing the Row IDs as text in Excel to maintain their accuracy. When I needed to use these Row IDs to add comments to specific rows in Smartsheet, I used the
int()function in Power Automate to convert the text-formatted Row IDs back into integers. This way, I could pass the correct integer format to the Smartsheet API for operations like adding comments to rows.
By keeping the Row IDs as text in Excel and converting them only at the point of use in Power Automate, I overcame the limitations imposed by Excelโs handling of large numbers.
-
Hello @jmyzk_cloudsmart_jp
Thank you very much for your response!
I am trying to insert a comment into the specific row id (converting the text into the integer format), but I havenโt had any success so far.
I have doubled checked the row id recorded in excel with smartsheet and confirmed that itโs correct.
Is there a possibility that I might be missing something?
The error message:
Row ID in Excel
Function
Thank you!
-
In my flow, the "List rows present in a table" action precedes the "For each."
I can not see your "For each" item, but you can get each row of the table with an expression like this;
outputs('List_rows_present_in_a_table')?['body/value']
Then, you can specify the parameters of the "add a discussion to a row" action, using the current for each item.
items()?['Comment']
-
@jmyzk_cloudsmart_jp Thank you
