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.
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.
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.
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.
