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?
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 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.
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
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:
Copy to Clipboard
Getting data from the Spreadsheet
Copy to Clipboard
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:
Copy to Clipboard
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 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.