How can I add conversation to a specific row id in Power Automate using excel as connector?

Anna Makhina

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)

jmyzk_cloudsmart_jp

Hi @Anna Makhina

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.