Sharepoint with Excel & Javascript read, edit & save

Question

Sharepoint with Excel & Javascript read, edit & save

Dariusz Biskupski 1

Hi

I am making HTML & CSS site on Sharepoint which will be saved as .aspx file. I want it to have a form so many people will be able to save it to one Excel. Each person submission would add new row and save the file. Another website would be pulling the content of this Excel and displaying it also on HTML, CSS page. I want to use JavaScript to do so. I see some external libraries to read the file and display on a page - this works fine, but the problem is with updating & saving the existing excel. Please help.

Regards
Dariusz

0 comments

1 answer

Your answer

Answer 1

RaytheonXie_MSFT 40,496 Microsoft External Staff

Hi @Dariusz Biskupski ，
I will recommend you to use graph api to edit Excel file. Here is the example to refer

const options = {  
	authProvider,  
};  
  
const client = Client.init(options);  
  
const workbookTableRow = {  
  values: "[  
    [1, 2, 3],  
    [4, 5, 6]  
  ]"  
};  
  
await client.api('/me/drive/items/{id}/workbook/tables/Table1/rows')  
	.version('beta')  
	.post(workbookTableRow);

You can add rows to the end of a table. Note that this API can accept multiple rows of data. Adding one row at a time can affect performance. The recommended approach is to batch the rows together in a single call rather than inserting single rows.
Here is the document for more details
https://learn.microsoft.com/en-us/graph/api/table-post-rows?view=graph-rest-1.0&tabs=http

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Dariusz Biskupski 1 Reputation point

2022-01-10T12:33:56.027+00:00

Thank you @RaytheonXie_MSFT for the option and help. I am afraid I am looking for an option where people can submit one set of data at a time. Moreover, it is company Sharepoint and I would like to avoid reaching external APIs. I was hoping in Sharepoint of Excell there is something supporting JavaScript developers that I could do it locally & in one folder on Sharepoint.

Thanks
Dariusz
RaytheonXie_MSFT 40,496 Reputation points Microsoft External Staff

2022-01-12T06:11:47.98+00:00

Hi @Dariusz Biskupski ，
Per my research there is no such function to edit excel file in sharepoint. You might can use xlsx-populate to achieve your requirement
https://github.com/dtjohnson/xlsx-populate
RaytheonXie_MSFT 40,496 Reputation points Microsoft External Staff

2022-01-13T08:27:29.97+00:00

Hi @Dariusz Biskupski ，
I am checking to see if the problem has been resolved.
Dariusz Biskupski 1 Reputation point

2022-01-13T14:08:05.23+00:00

Thanks @RaytheonXie_MSFT .. The reply button doesn't work so I reply here.

Thanks for following up. I checked that library. It seems to have the same difficulty as other libraries alike - they cannot read, write and save excel file. They either read or create new one. I think it's the browser security restrictions that doesn't let to save any files, all of them create new file & download rather save to existing file or overwrite. I was originally hoping everything can be coded in aspx file which will display the site on Sharepoint, send form data to excell and display that data from the Excel file on the same page. It seems much clear now this is impossible.

In this case, I am investigating using Power Apps. Would you know if I can create a custom form with PowerApp (but without use of MS Form), add the new entry to the list on Sharepoint (instead of Excel) and then display it on a page ? perhaps the page itself also created with Power Apps?

Thanks
Dariusz
RaytheonXie_MSFT 40,496 Reputation points Microsoft External Staff

2022-01-14T07:21:38.913+00:00

Hi @Dariusz Biskupski ，
Since your issue is about Power Apps. I will recommend you to raise a new ticket in Microsoft dedicated forum for power app
RaytheonXie_MSFT 40,496 Reputation points Microsoft External Staff

2022-01-17T08:23:29.84+00:00

Hi @Dariusz Biskupski ，
would you please provide us with an update on the status of your issue?
Dariusz Biskupski 1 Reputation point

2022-01-17T10:20:48.45+00:00

Thanks @RaytheonXie_MSFT . <My issue is not about Power apps, it's about saving data to an existing Excel file via the browser and this is impossible as I found out so please close the case if that's what you are asking for. Ppower App is a potential workaround solution to this problem. Thank you for all your help.
RaytheonXie_MSFT 40,496 Reputation points Microsoft External Staff

2022-01-18T08:08:27.337+00:00

Hi @Dariusz Biskupski ，
Per my research, You can try Patch(DataSourceName,Defaults(DataSourceName),{Field1: "Value1",Field2:"Value2"}) to add a row to excel.
For more information about Patch, please refer:
https://learn.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

Share via

Sharepoint with Excel & Javascript read, edit & save

1 answer

Your answer