Hello, this is my first time using the community, and I hope that I can also be helpful to some of you.
I am building a flow that will basically do the following:
- Export Invoices from Concur
- Branching by different approval statuses
- Lookup the Invoices in NS leveraging the reference ID or Invoice ID
- Creating an Excel file with some sort of reconciliation. This is where I have a question.
My excel "lives" in SharePoint, and I want Celigo to Import: Reference ID, Vendor Name, Amount per NS, amount per Concur (all already in the data and I can easily obtain, Calculate the Difference to hopefully get to 0.
I connected to Microsoft Excel Online using a "simple connection" but I cannot seem to find out how to connect to my specific file. I looked through the documentation and couldn't find how to 1) Connect correctly 2) Setup the import step correctly
Have you tried using this template I made in the marketplace? Working with Excel APIs can be tricky, so I made this template to easily extract and load data.
Hi Tyler, I tried but I am getting an error when trying to install it: Failed to create Import for _id "66795a680b17e44a54d4b337" due to: Please update your connection resource and set the concurrency level field to 1 (i.e. located at the bottom of the HTTP form in the advanced section). If concurrency level is not equal to 1, then this import could experience race conditions, and produce corrupted data.
I am reading this but it does not explain how to get to that screen where you select the Excel file.
My issue is that I don't know what Resource and what API Endpoint I need let alone the item id.
I selected Worksheet and worksheet collection add, but I am not sure if that is correct. For the item id I added the id of what I hink is the correct one from my URL in the browser.
I am trying to run the flow and have it create 5 columns: Invoice ID, Vendor Name, Amount per NS, Amount per Concur, Difference (Concur minus NetSuite).
I created a ticket but the person didn't know 100% either. So I am still not clear on how to actually find the Import that I neede or how to set it up. I do have the correct scopes.
Did you change your connection's concurrency to 1? You need to go into the connection, flip to HTTP view, go down to the advanced section, then edit it to 1.
Once you do this and install the template, you don't need to worry about much else. The template I made fetches the list of SharePoint sites for you to select, then populates a list of files to choose from. Are you needing to make new workbooks, sheets, and column headers every time?
Hi, so I was able to get to the screen you are showing. I selected SharePoint, the Worksheet name, then I saved it but it does not show my workbook. Am I missing anything? when I open the drop-down list, it only shows Please Select Data but there is no data. I know the connection works because it shows all the SharePoint Sites I have access to.
On your connection you have all of these scopes added?
Files.ReadFiles.ReadWriteoffline_accessSites.Read.AllSites.ReadWrite.All
Yes, I have those. What I find interesting is that every single SharePoint Site shows up, but not the Workbook.
This is the error that I am getting when I run it without the Workbook.
Failed to load spreadsheet. Unable to lookup the range needed for insert. Root error: {"code":404,"message":"{"error":{"code":"itemNotFound","message":"Item not found"}}"}
Just to document it here. I attended the office hours, and you suggested to give you access to check the script in the template itself in our environment. We gave you access to our Celigo environment. I also sent you an email confirming this.
thanks,
Mauricio
@tylerlamparter just wanted to thank you for all the help you have provided. For transparency, I have created a flow that successfully creates 7 different worksheets that compares the invoices from Concur and NetSuite.
My follow-up question would be: is there an import that rather than can create a new file everytime it runs? I would love to run this flow weekly where a new file is created, the reconciliation is created, and someone from our team simply reviews the data for any issues. Let me know your thoughts. Thank you.
To clarify, the flow now populates 7 existing workbooks and/or worksheets, but you're wondering if this can be modified so on each run it makes a new workbook and/or worksheets that it populates? I assume each run the workbook would get some date put into the name?
The flow extracts data from Concur and branches out 7 different approval status. Right now, I indicated what file to go to in my SharePoint, what worksheet to update. So only 1 already existing workbook and just updates the 7 worksheets.
From a reconciliation and audit trail standpoint, ideally the flow creates a new workbook every time it runs (weekly) by branch / status or 1 workbook with 7 new worksheets (1 per branch / status).
This way, I don't have to modify the flow weekly. I could set it and "forget it". Did I explain correctly?
The best approach would be to create a flow (Flow 1) that generates a new workbook and its worksheets. The simplest way to do this is by using a template workbook and copying it via Microsoft’s Copy API. Once copied, you can rename it and grab the new workbookId.
Then, update Flow 2’s settings with this workbookId via an API call. After Flow 1 finishes, trigger Flow 2 using the “run next flow” setting in Flow 1. Flow 2 will handle pulling the data and populating the appropriate sheets.
You’ll need to stop using the simple settings I originally built, since the workbookId now has to be dynamic. To disable those settings, open any script, search for useAsPrimaryInterface, set it to false, and save.
Then go to the imports and update the relative URI—change settings.import.workbookId to settings.flow.workbookId.
