M365 - Create Session for Excel Update

Questions
1

Describe the problem/error/questionSorry if this was (most likely) asked before, but I am at my wits end here.

I have a workflow that should read an excel file from a Teams Channel, check for certain entries, do ABC and then write back into the same excel file. Super easy to do in Google Sheets, an absolute nightmare with M365.

After figuring my way around the HTTP Response nodes I have pretty much managed to achieve everything besides updating the excel file in Sharepoint. I see that HTTP Request nodes are preferred, I see that designated Sharepoint nodes dont really support writing into a specific column in a dynamic row in Excel, and downloading a file as binary and then changing the content before writing an entirely new file is just not feasible. Oh, and the Excel node only works for my OneDrive, not my Teams Channel, so a big fat “no” here as well.

Now, I learned from online discussions that I am supposed to start a Workbook Session in order to “unblock” the excel file before I can write back any changes. I tried that and failed miserably as you can see in the error below:

r/n8n - N8N <> Sharpening - Create Session to write to Excel file....|750xauto

POST https://graph.microsoft.com/v1.0/drives/{{ $('Get Drive ID').item.json.value[0].id }}/items/{{ $('Get File ID1').item.json.id }}/workbook/createSession

The thing is, I am the owner of the workbook, parent directly and entire channel and Team, I have full read/write access, my Enterprise app can read write to .Files and .Sites, there is no version lock in the library and nobody has the file open / locked in another process. There is no reason for me not to be able to start the session, yet here I am.

Can anyone advise me on what to do here please? Really need some help >.<

What is the error message (if any)?

Forbidden - perhaps check your credentials?

Contact the workbook owner to request edit access.

Please share your workflow

Information on your n8n setup

  • n8n version: latest
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n: selfhosted - Docker
  • Operating system: LAMP
2

Hi @patrickmau! Welckmw Back to the n8n community :sparkles:. Make sure your Microsoft OAuth credentials have the correct Graph scopes like Sites.ReadWrite.All and delegated file permissions and then re authenticate the credential. If it still fails the usual workaround is to download the file via SharePoint or Graph update it locally using a Spreadsheet File node and upload it back since direct session writes are not reliably supported for Teams or SharePoint files.

3

Hi @tamy.santos , thank you very much for your insights, really appreciate it.

I can confirm that all API permissions are correct and that full .site.ReadWrite.All and .files.ReadWrite.Allpermissions have been given via delegated access. This should work, credentials have been reconnected already but the same 403 error persists.

I have successfully downloaded the file via Sharepoint Download File but struggle to make sense on how to edit the file. I did manage to extract via Extract from File (XLSX) to JSON in order to be able to manipulate the date. Using various Excel nodes, I could not find a way to manipulate the data and write it back. What nodes would be recommended to achieve this before ultimately uploading the file again?

Appreciate any guidance you may have, thank you very much!

4

@patrickmau

Happy to help! You’re really close to getting this working.After you extract to JSON, I’d add a Code node to make your changes. Here’s an example of what I typically use:

const items = $input.all();
return items.map(item => {
item.json.Status = ‘Processed’;
return item;
});

Then I’d add a Spreadsheet File node with operation “To File” and format XLSX. Make sure to enable headers and it’ll create your Excel file from the modified JSON.

Finally, just upload it back to SharePoint at the same file path to overwrite the original.

I’ve found this approach works much better than the Excel nodes for SharePoint files since it avoids those session token issues entirely. We’re basically treating it as a regular file workflow instead of trying to use the Excel API directly.

5

Thank you Tamy, this helps a bit.

I did manage to update the JSON values I extracted and now added this back into a Convert to XLSX node and managed to create an Excel file, but sadly this file only contains a single row, namely the row I was trying to update.

The issue here is that I am downloading a file with eg. 100 rows, filter out the first row where Status equals ““, then manipulate that item.json.status value, and now want to update only that one row in the table containing the 100 rows. Once that update is done, I need to get that file back to Sharepoint.

I am getting rather cranky over all these Sharepoint problems, I am thinking to just move the file to my personal OneDrive and use the native Excel node where I can just update a row where a certain column value matches a condition. Still, not giving up on this yet!

Any guidance is appreciated, thank you very much for your time.

1 Like
6

Hi @patrickmau ! I hope your doing well.

You should try this solution below!

If you want to update all matching rows

const items = $input.all();
return items.map(item => {
if (item.json.Status === ‘’ || item.json.Status === undefined) {
item.json.Status = ‘Processed’;
}
return item;
});

If you want to do first match only

const items = $input.all();
let found = false;
return items.map(item => {
if (!found && (item.json.Status === ‘’ || item.json.Status === undefined)) {
item.json.Status = ‘Processed’;
found = true;
}
return item;
});

after the code, you should do this.

format XLSX, enable “Include Header Row”. This converts all 100 rows back into an actual Excel file.

upload the binary back to the same file path in the Teams channel to overwrite the original.

7

Happy to help.
I wish I could insert a GIF here of John Cena saying “are you sure about that?!” :joy:

1 Like
8

Yea, gif support would be nice!