Is there a way to programatically edit Office 365 Excel documents?

Question

I have to update a spreadsheet that looks like this.

It's not hard at all. I just go to the last line and enter what I need in the appropriate columns. I'd like to automate this. The first thing I tried was Selenium Webdriver but the results are too slow and hit and miss. The next thing I checked was for an API but it looks like there is nothing for Excel.

I could use Selenium to download the file and then use a C# app to modify it as an excel file and then reupload it. At the point though I'm wondering if Google Docs has an easier solution.

So is there any way to programatically modify an excel online spreadsheet?

Verner

edit how/where? you want to edit it online? or download a .xlsx and change that locally? – Gentlewoman

Ideally just call a rest API and it gets updated. – Verner

Answer 1

I'm aware of 4 options to programmatically modify Excel Online via JavaScript or REST:

1. Office Add-ins platform: https://dev.office.com/docs/add-ins/overview/office-add-ins
2. Excel Services JavaScript API - EWA Excel Web Access Namespace (not updated in couple years): https://msdn.microsoft.com/en-us/library/hh315812(v=office.14).aspx
3. Excel Services REST API (via SharePoint Online): https://msdn.microsoft.com/en-us/library/ee556842(v=office.14).aspx
4. Microsoft Graph REST API (Excel Objects are currently in Beta): https://graph.microsoft.io/en-us/docs/api-reference/beta/resources/excel

Villenage

As far as I've found, you cannot modify Excel Online documents with the Excel REST API – Gunman

any full samples about it ? 3 and 4 options ? – Cordwood

Answer 2

I don't know from when is possible, but now you can add rows to tables and is quite easy to test on the graph explorer.

Remember that first you need to set up a table via api or in the document.

From docs:

Adds rows to the end of the table. Note that the API can accept multiple rows data using this API. Adding one row at a time could lead to performance degradation. The recommended approach would be to batch the rows together in a single call rather than doing single row insertion. For best results, collect the rows to be inserted on the application side and perform single rows add operation. Experiment with the number of rows to determine the ideal number of rows to use in single API call.

Aesop

Answer 3

The easier way I've found is using the sheet2api JS lib, e.g.

import Sheet2API from 'sheet2api-js';

const url = 'https://sheet2api.com/v1/FgI6zV8qT121/characters/';
const updateWithData = { "Favourite Thing": "Beer", "Name": "Bugs Bunny" };
const options = {query: { 'Name': 'Bugs Bunny' }};
Sheet2API.update(url, options, updateWithData).then(function(result){
  console.log(result);
}, function(error){
  console.log(error);
});

It interacts with the Microsoft API and handles all the auth keys.

Tc