Welcome back to Continuous Improvement, the podcast where we explore different strategies and techniques to enhance our productivity and achieve constant growth. I’m your host, Victor, and today we have an exciting topic for all our business users out there who love working with spreadsheets. We’ll be discussing how to write simple scripts in Google Sheets to revolutionize your data analysis and make powerful API calls. So, let’s dive right in!

Picture this scenario: you’re a business user who loves using Excel as your go-to tool. You’re comfortable writing functions, calculating in spreadsheets, and even creating visualizations. But what if I told you that you can take it a step further and make API calls directly from Google Sheets? No coding required! In this episode, I’ll guide you through the process of writing simple scripts to fetch data from APIs, enabling you to perform more powerful tabulations and analysis. And the best part? It’s all done within the user-friendly environment of Google Sheets. No need for complex installations!

Firstly, open a new blank Google Sheet. We’ll use two cells as input coordinates. In this example, I’ll let B1 and B2 represent variables for datetime and date, respectively.

Secondly, to create a button, click on “Insert” in the top navigation toolbar, then select “Drawing.” Draw a rectangular button labeled “Get Air Temperature.” Save and close the drawing to create the button.

Next, right-click on the newly created button and select “Assign a script.” For now, let’s input the value “getAirTemperature.” We’ll create the method for this script later.

Moving on, in the navigation bar, select “Extensions” and then “App Scripts.” Here, we’ll write JavaScript code to perform the magic, similar to creating a Visual Basic macro script in Microsoft Excel.

In the code editor, copy and paste the provided script. Don’t worry if you don’t understand all the details—we’ll go through it together. This script acts as a trigger when the button is clicked. It retrieves input values from the Google Sheet, prompts the user for confirmation, makes an API call using the UrlFetchApp.fetch function, and displays the output in the spreadsheet.

The first time you click on the button, you’ll be prompted to give permission for this custom script to run. Since this is for development purposes, click “Continue,” log in, and choose the option to continue with unsafe settings.

Next, an alert will pop up asking for confirmation of the input values retrieved from the Google Sheet. Click “Yes” to continue. If everything goes well, you should see a success confirmation. The temperature values from the API call are then displayed in the Google Sheet.

If you’re a developer rather than a business user and prefer working with your own code editor, such as VScode, you can edit the script in a more familiar environment. You can even use the command-line interface tool called “clasp” to develop locally. This allows for better source version control. To get started, make sure you have Node.js installed and run the following command in your terminal:

npm install @google/clasp

Then follow the instructions in the documentation, specifically the guide for clasp, to set it up. Once you’ve installed clasp and configured it, you can clone your script using the command:

clasp clone <your Script ID>

You can then make changes in your preferred code editor, such as VScode, and upload the new AppScript using:

clasp push

Note that you may need to enable the Apps Script API by visiting https://script.google.com/home/usersettings. This will ensure a smooth process.

To summarize, Google Sheets is a powerful tool for computations, and being able to make API calls directly from it opens up even more possibilities for complex data analysis. You can leverage Google Sheets as a frontend for calculations and provide a user-friendly experience for business users, rather than relying on custom-built UIs. Feel free to reach out if you have any questions about making API calls from Google Sheets. And remember, happy coding!

That brings us to the end of another insightful episode of Continuous Improvement. I hope you’ve learned something valuable today and are inspired to explore the endless capabilities of Google Sheets. Stay tuned for more episodes where we’ll continue to uncover strategies for personal and professional growth. I’m Victor, your host, signing off until next time. Keep improving and embrace the power of continuous growth!