Implement full application logic in a single Excel file

In this blog post, we described how the SpreadsheetCloudAPI service helps our users to create a full-featured web application without implementing complex calculation logic. The necessary logic for that application is provided in a single Excel file. This blog post describes how to create this Excel file.

The application contains three tabs with a chart on each tab. We created an Excel file with the “Materials”, “Prints”, “Users” and “Chart” sheets.

The «Materials» sheet

Use this sheet to enter information about materials used for 3D printing.

  • Type — 3D printing plastic type: these values are used in the «Type» column on the «Prints» sheet
  • Density — plastic density
  • Diameter — plastic filament diameter
  • Cost — price per kilogram of plastic

This sheet is used to enter data only; no calculations are performed here. These values are used in the «Prints» sheet.

The « Prints» sheet

We use this sheet to calculate the cost of printing each model.

  • Name — name of a user printing a 3D model
  • Type — type of plastic used
  • Length, m — plastic filament length used to print a 3D model (the 3D printer’s software gives this value)
  • Cost — cost of printing a 3D model. You need the cost and weight of the material used to print a model to calculate this value.
     
    We created the following columns with these additional values:
  • Mcost, Mdensity, and Mdiameter — material cost, density, and filament diameter.
    We can get all the specified material type’s parameters (the “Type” column) from the “Materials” sheet using the VLOOKUP function.
     
    Mcost — =IF(A2=””,””,VLOOKUP(B2,Materials!$A$1:$D$3,4,0))
    Mdensity — =IF(A2=””,””,VLOOKUP(B2,Materials!$A$1:$D$3,2,0))
    Mdiameter — =IF(A2=””,””,VLOOKUP(B2,Materials!$A$1:$D$3,3,0))
  • Msquare — sectional area of the material filament. Calculate it using the following formula: =IF(A2=””,””,G2*G2*PI()/4) 
    G2
     — reference to a cell containing the filament diameter.
  • Mweight — weight of the material used to print a 3D model. Calculate this value as a product of the filament sectional area, material density, and filament length:
    =IF(A2=””,””,H2*F2*C2)

Now we have all the values required to calculate the cost of printing a 3D model. Enter the corresponding formula in the “Cost” column:

=IF(A2=””,””,(I2*E2)/1000)

This formula uses references to the following cells:

I2 — weight of the material used, in grams

E2 — cost per kilogram of material

We extended this formula to more rows in the “Cost” column (for example, 3000) to allow printing a lot of different models. When entering values in the “Type” and “Length, m” columns on the “Prints” sheet, the corresponding value in the “Cost” column is automatically calculated.

Note that the SpreadhseetCloudAPI service’s notifications automatically adjust references to ranges used in formulas when information on new material is added as a new first row in the table.

The “Users” sheet

This sheet lists all the users and shows the total cost of all their 3D models.

  • Name — a username
  • Full Cost — the total cost of all models a user prints. Use the SUMIF function to calculate this value:
     
    =IF(A2=””,””, SUMIF(Prints!$A$1:$D$3000,A2,Prints!$D$1:$D$3000))
     
    We extended this formula to more rows in the “Full Cost” column (for example, 200), to provide the capability to enter additional users.

The “Chart” sheet

This sheet visualizes and summarizes the data on the “Users” sheet using a chart.

The Excel file is ready. You can find it here.

The next step is uploading the Excel file to the SpreadhseetCloudAPI service. Below is a brief tutorial on how to use the service.

1. Log into the SpreadsheetCloudAPI server’s administration page (http://spreadsheetadmin.azurewebsites.net/) using your SpreadsheetCloudAPI account by clicking Sign In, or Sign Up to create a new account.

2. Either click Create your first app (if there are no existing applications) or the New menu item.

3. Provide the necessary values for the Application Name, Application Website, and Application Description fields:

  • Required. The Application Name field specifies your application’s name within the SpreadsheetCloudAPI service.
  • Optional. The Application Website field specifies your application’s website. This value is treated as a comment so you could identify your application faster.
  • Optional. The Application Description field specifies additional information on the application.

Click Create to create an application with the specified settings and generate its API Key.

4. After the application has been created, the API Key is automatically shown on the application’s page. It is required to communicate with the SpreadsheetCloudAPI service.

5. Upload the template file to your application by clicking Upload and selecting your file in the Open File dialog.

If you have any questions or comments regarding anything we mentioned in these blog posts, feel free to contact us scloudapi@gmail.com