Use SpreadsheetCloudAPI to write applications and make life easier

Hello! Our team has a hobby - we love 3D printing. And not just love, but actively print everything and everyone - from simple toys on the table to parts that are not even sold in China. Everyone in our room at least once printed something.

In this article I would like to share our experience in solving the problem of purchasing plastic for a printer and how the SpreadsheetCloudAPI service helped us with this. Details under the cut.

Just as a simple printer needs ink, a 3D printer needs plastic. Initially, we printed plastic that came with the printer. Then they chipped in equal shares and bought a bunch of multi-colored coils. However, all printed in different volumes, and it would be unfair to fold equally in the future. Therefore, we simply added an Excel file, where everyone wrote how much plastic he spent on each print, and when purchasing, we proportionally calculated the share of each.

And everything would be fine, but over time the number of people who started using the printer increased, especially after setting up a remote start of printing. As a result, it became terribly inconvenient to use this Excel file. So the idea of ​​writing some program was born that would allow everyone to simply write down their print sessions, and before buying someone would say how much to throw off.

In the process of thinking about the ideas of the application, we came across a service that would be written by our clients, using our Spreadsheet Document Server: SpreadsheetCloudAPI . It will essentially allow you to use our current Excel file as the basic part of the application, and not to think about data storage and calculation logic at all. In addition, it is free.

So, what we need to get from the application:

• Enter and edit print information,
• The ability to add new users,
• The ability to add new print materials (you can print from different plastics and they all have different costs),
• And most importantly, to show the calculation of how much money should be thrown off at the next purchase.

As a result, the UI that we want to get should look something like this:

Yes, it is no different grace, but we are not going to sell it. And for our needs, it fits perfectly.

Cost calculation

The most important problem in calculating the cost was that plastic is sold by kilograms, but consumed by meters. At first, we even thought of using scales, to measure the weight of each figure, but we are programmers, we need to come up with something more elegant, and we remembered a school course in physics. Plastic has a density, a bar has a diameter, we know the length spent, and as a result, we can calculate the mass of each figure.

Thus, user actions should be minimized: record the printing session, which plastic was used and how long it went. Everything! If you know the characteristics of plastic, then without problems, the weight can be calculated independently. And yes, all this can be done in the same Excel file, and do not write a single line of code! We store data about available materials on a separate sheet, and the weight calculation formula takes them from there.

Implementation

Backend

In fact, our backend will not do anything except communicate with the SpreadsheetcloudAPI service: it sends the entered data to it, and takes the converted parts of the document back. To implement the server side, php was chosen.

To communicate with the service, we need only three parameters:

• Service URL - ' http://spreadsheetcloudapi.azurewebsites.net/api/spreadsheet ',
• API Key - the key of our application, which gives us the service,
• File name.

For convenience, we have collected them all in one class:

class PrivateConst { const Base_Url = 'http://spreadsheetcloudapi.azurewebsites.net/api/spreadsheet'; const API_KEY = 'API_KEY'; const File_Name = '3D.xlsx'; } 

Communication with the service will occur via the Web API. In principle, the API is fairly simple and normally documented. For example, to add cells to a table (what we need when creating a new print record), we just need to send a JSON of the form:

 { "id": "some_id", "filename": "test", "extension": "xls", "sheetindex": 0, "sheetname": "Sheet1", "startrowindex": 0, "startcolumnindex": 0, "endrowindex": 3, "endcolumnindex": 5, "mode": "ShiftCellsDown", "formatmode": "FormatAsPrevious" } 

at / api / spreadsheet / insertcells via PUT request.

When we got the data, we even managed to get bored, because the service itself can give us the required cells in HTML format ( Export to HTML ). As a result, we don’t even have to think about how to display data from a document. Getting this HTML is also not difficult:

 function getSessionHtml($id, $sheetName, $rowLimit, $columnLimit){ $params = array( 'id' => $id, 'sheetname' => $sheetName, ); if($rowLimit > -1){ $params['endrowindex'] = $rowLimit; } if($columnLimit > -1){ $params['endcolumnindex'] = $columnLimit; } $request = get($params, '/exporttohtml'); return $request; } 

where get is our method that sends a GET request with passed parameters to the passed address.

Among the parameters of these methods is the $ id - ID session of the downloaded file. It allows us not to spend resources on opening a file each time, and it is possible to execute commands and requests in turn, saving time and requesting.

Frontend

As I already wrote, the data from the SpreadsheetCloudAPI service we get in the form of ready-made HTML. When exporting, data is a table, and to edit this data, we just need to process the click on the table cell and hang the editor we need on it. To edit the values ​​we use “Input”, to select values ​​from the already existing “Select”.

When creating a new print session, we will need to select a user name, a material name, and enter a length value:

Of course, the data can be edited:

The “Cost” column will not be edited; it will be calculated directly on the service and filled in during the export.

No calculations are performed on the “Materials” tab, we just set the values:

On the Users tab, the only parameter we will create and modify is the username. The column “Full Cost” will be calculated and not intended for editing. Cherry on the cake, we decided to show the chart, which clearly shows who we have the most typing. The chart is also drawn by Excel, and we ship it just with a picture from the service.

That's all. Quickly and easily, we created an application that was based on our previously created Excel file, and this allowed us not to write a single line of code to implement the application logic and store data, all we needed to do was a UI.