Integrating Google Sheets with the SkyCiv API

Wouldn’t it be nice to run your structural analysis, design, and optimization right in Google Sheets? Wouldn’t it save a lot of time to have a simple tool that uses a handful of parameters as input to create and analyze a complete structural model?

In this article we present the integration of the SkyCiv API with Google Sheets, using Google Apps Script. Apps Script is a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products, so you can easily create your own apps to automate repetitive tasks combining all the capabilities of Google Products and any other service capable of communicating via HTTP requests. This is the case of SkyCiv, our API receives an object containing the model information and the functions SkyCiv is supposed to run on the model. After all the calculations are performed, the API responds with the results of the analysis, design, and optimization depending on what was it asked to return.

This solution rises from the problem of having to manually create the model of the structures your company typically designs and having to manually get the results from the analysis/design/optimization software to a spreadsheet to further process those results. As an example, we are going to explain how to create a Spreadsheet capable of creating a retaining wall over piles model in SkyCiv S3D, running analysis, and getting the results back to the Spreadsheet.

The model consists of a plate that represents the wall stem on top of a footing beam which is supported by two piles. It is a pretty simple structure but modeling it from scratch every single time can be a very tedious task. Here is the step-by-step guide to getting a Spreadsheet communicating with the SkyCiv API and automating the modeling and analysis processes.

Note: The Spreadsheet used in this example along with all the code related to it, can be accessed here, keep in mind that you need to make a copy in your Drive first by going to File>Make a copy

The input

The first step is to identify the set of parameters that can fully describe the structural system. In the case of the described retaining wall, we need to geometrically describe the stem, the footing, and the piles, additionally, we need the properties of the supporting soil as well as the loads that will be applied to the wall. That said, the parameters that describe the model are organized in some tables in the first Sheet.

The code

All the code is written right in the Apps Script user interface. In order to get to the code editor, you can go in the menu bar to Extensions > Apps Script

User interaction

For the user to interact with the functions that we are going to write we can add a menu Item to the UI, that can be easily done using the following code:

Getting data from the Spreadsheet

In the code, we will need to have all the data related to the geometrical parameters, loads, and support conditions from the spreadsheet, for that purpose, we create an Apps Script object (very similar to a JavaScript one) using the structure shown in the following snippet:

Creating the model

The most important function is the one that creates the model object that will be sent to the SkyCiv API, it follows some rules that can be found in our API documentation. We need to write a lot of for loops and conditional statements to successfully create that model object (nodes, members, sections, loads, etc...). All the code can be found here.

Creating the API Object

The model created in the previous step needs to be plugged in an API Object which contains all the instructions for the API so it knows what to do, the general structure of the API object can also be found in our API documentation. For this example, we will start a session, set the model, mesh the plates, solve, and save the model. All the code can be found here. The API Object also contains the authentication data, which consists of your email registered in SkyCiv and your key, in case you are running the Example spreadsheet, make sure you fill out that infromation in the Auth Sheet.

Making the request to the SkyCiv API

As mentioned before, this API object will be sent to the SkyCiv API using a POST HTTP request, that request is performed using the following code:

The ‘response’ object will contain what the API responded to the request, that response can be further processed in the Spreadsheet or in the code according to your specific needs. It includes reactions, displacements, stresses, internal forces, etc...

The results

The first time you are running the script, you need to authorize it, and once the model is created and analyzed, the results come back to the spreadsheet in the way you specified in the code. The whole spreadsheet looks like this:

The script will get a URL where the model was saved, when you go to that URL you will be able to verify, modify and solve the model, all in the SkyCiv S3D user interface:

The best thing about this integration is that you can significantly reduce the time spent modeling these typical structures. By simply adjusting the input parameters you can get a ready-to-solve model and further automate results analysis, design exploration, design checks, and optimization by combining the best of the SkyCiv API, Google Sheets and Apps Script.

Happy coding!