Multiline Cells with Make and Google Sheets in Three Easy Steps

ByTomas Zezula

Oftentimes we are faced with a challenge of transforming a complex object into a database record. In fact, that is not a big deal because most databases allow you to store data structures like arrays or nested objects. When it does become tricky is when you care about visualisation as well. And this is exactly the case with a spreadsheet. Not only do you want to store an array in a spreadsheet cell, but you also want the record to be easily readable.

TL;DR

Watch my short tutorial packed with all the information you need. Please give me thumbs up when you are done watching. Thank you.

Check out my template on GitHub and rock on your own!

What are We Going to Build?

Imagine a store inventory as a collection of goods where each item is a product identified by its name and a quantity unit. More importantly though, each product comprises a set of specific items. For example, when we go to a supermarket we don’t just buy “apples”, we typically have to choose a certain kind. Same goes for mangoes etc. Each product therefore has an array of items.

Click on the GIF below to see the gist of it.

JSON array of items transforms into a multiline cell in Google Sheets.

Step 1 – Transform an Array to a String

The first step is to convert the array into a comma separated string. The array map() function does precisely that.

Array map() takes a complex array and transforms values of a given key into a comma-separated string.

Step 2 – Replace Commas with New Line Characters

Next, replace the comma separators with newline characters using a spreadsheet function called SUBSTITUTE.

Leverage formulas in Google Sheets and experiment before you bring the transformation into your Make scenario.

Step 3 – Add the Transformation to Make

Surprising as it sounds it is perfectly plausible to write spreadsheet formulas directly in Make! Just make sure that in your Google Sheets > Add Row module the dropdown Value input option is set to User entered.

Transformations are a powerful feature. Exploring scripting possibilities in your target module is always a good idea.

That’s it! Thanks for reading this quick tip on Make and Google Sheets. I have created an easy-to-use template on GitHub, check it out. Watch my tutorial on YouTube and give me thumbs up if you find it useful. Cheers.

Similar Posts

JWT embraces trust
| |

Conquer Authentication with Ktor: Part 5 – Introduction into JSON Web Tokens

ByTomas Zezula

Welcome back to our ongoing series on the Ktor framework and its impressive capabilities when it comes to web application security. In this article, we shift our focus from traditional form-based authentication to a modern, innovative one – JSON Web Tokens (JWT). JWT provides several key advantages that have led to its growing popularity. Firstly, its stateless nature promotes better scalability by eliminating the need to maintain user sessions. Secondly, JWT’s JSON structure guarantees cross-platform compatibility. This means your authentication mechanism works equally well for web apps as it does for mobile apps or other systems. Lastly, JWT proactively increases the security of your applications, preventing attacks like CSRF. So, join us today as we delve into the world of JWT, exploring its mechanics and benefits.

No code dilemma.

The No-Code Dilemma

ByTomas Zezula

Using low to no-code platforms offers benefits in terms of increased efficiency and rapid development, but it’s essential to recognize their limitations and use them only when appropriate. Here’s a brief overview of some downsides of using no-code and a scenario where it can add business value without compromising good software development practices.

Authentication with Ktor
| |

Launching a Guide to Conquer Authentication with Ktor

ByTomas Zezula

Welcome to the first post on the subject of authentication with Ktor. As developers, we appreciate the importance of security, but integrating it smoothly within our backend services can sometimes seem like a daunting task. However, when working with Kotlin and Ktor, the task becomes much more approachable due to concise syntax and flexibility. This series aims to delve into the diverse authentication mechanisms. We will find our way through Basic Auth, Form Auth, to more complex ones like JWT and OAuth. Whether you’re new to Ktor or looking to broaden your horizons, this series will offer practical, hands-on experiences to help you effectively bring security to your backend applications.

Featured image
| |

Streamlining Console Output Verification with Kotest

ByTomas Zezula

Console logging is often perceived as a bad practice, but in specific contexts, it’s quite suitable. Consider a scenario of a workshop where printing output directly to the console enhances transparency and simplifies the project setup. Using a comprehensive logger could lead to unnecessary clutter. However, there’s a downside to directly printing to the console—it makes it challenging to verify the result. Well, at least so I thought until I accidentally stumbled upon this awesome article by Thijs Kaper. Although Spring Boot provides out-of-the-box support for output verification, it’s also possible, and sometimes preferable, to implement it independently. In this blog post, I’ll demonstrate how to create a custom spec in Kotest that you can leverage in your tests. This spec automatically captures console output and provides the result for verification.

Failed Gradle build
| |

Gradle Multi-Module Builds: Setting Up Shared Dependencies

ByTomas Zezula

When you are managing a multi-module Gradle project, more often than not you’d want to share certain dependencies across different modules. This is particularly true for test dependencies. You can achieve this by defining common dependencies in the root build file and applying them to all subprojects. However, a problem often encountered is the dreaded Unresolved reference: testImplementation error. This blog post suggests a simple trick that resolves the issue. Give it a go and happy testing ever after!