I've got the week off for some R&R, and today I'm going to start looking into a new addition to my 3D engine spreadsheet: opacity.

Here's the original, if you're curious: docs.google.com/spreadsheets/d

The problem I'd like to fix is that because it uses the SPARKLINE formula, everything on the screen is ultimately one line, which means lines might cross each other in weird ways, and that all objects are effectively transparent.

That's fine for simple shapes, but limits the options for more complex drawings/toys/games.

The first thing that comes to mind is that if we have our 2D shapes, we could try and see what points are inside a shape that is closer to the "camera". Take this simple 2D example of a square with a line through it:

While we only have the coordinates for the vertices of the drawing, in theory we can take each line, look at a large number of points within that line, then reconstruct it so that it goes around the shape, giving the illusion that the square is "in front", obscuring the line.

There are three difficulties with this that I'll need to dig into:

1. How can I take a list of X,Y,Z coordinates and determine what is/isn't a 2D solid?
2. How can I take a point and determine if it is inside a 2D shape?
3. How can I tell a spreadsheet to automatically draw "around" a 2D solid?

For Q1, it's easy enough to take the coordinates and check for a point that repeats (the start and end of a shape) then grab all of the points between them. But I have to find a way to do that without REGEXEXTRACT, because that doesn't work inside an array formula.

I *think* I can do it by taking the list of coordinates, splitting them into triplets, numbering each one, then using VLOOKUP to find the first iteration of each triplet. For triplets that appear twice, the VLOOKUP should return a lower integer than the assigned number, giving me a range to pull from the original list.

In theory, each row should list the triplets that make up the coordinates of a single shape, but for some reason now it's just giving me all of the triplets in each row.

In cases like this I tend to take each part of the larger formula and test them separately. Doing that pretty quickly points us to the SEQUENCE formula, giving us the same numbers over and over.

Oh oh oh, it's because I'm putting a sequence inside a sequence. That seems to cause the formula to only focus on the first sequence, flattening everything else and causing other parts to loop.

I've got to give myself a little break from this for now, but I'll come back to it later!

Didn’t actually get anything done today — spent most of the day reading that article and others like it.

They helped confirm I’m at least thinking about the problem correctly (I need to define planes then determine whether a given point is visible behind them), but I’m still stuck on how to get a spreadsheet to do that reliably. 🤔

Okay I have a stupid idea that probably won’t work but I’m gonna try it tomorrow anyway

Part 1 of this stupid idea involves calculating all of the points along each line in the 2D drawing. I've spent two days finding 50 ways how to not do this. 🙃

Part 2 will be finding all of the points that appear twice in the new list of points, then marking them based on how "close" they are to the viewer.

Part 3 will then be replacing the points between the "further" marked points, and replacing them with the points between the "closer" marked points.

The end result should be an optical illusion that makes objects look solid. 🤷‍♂️

Turns out that *in general*, arrays of difference sizes can't coexist nicely. *But*, using the new MAP function does let me take in multiple arrays and return strings based on a LAMBDA function, which can then be SPLIT, giving me arrays of different sizes. support.google.com/docs/answer

Fixed it! Of course it was a regex error on my part 😅

OK, so I've been spending a few days figuring out different ways that this *almost* works. Feels like I'm zeroing in on the right strategy, but for now I just wanted to say that the new LAMBDA functions are really great, and can be nested to achieve things similar to a QUERY function but *much* faster. Most of the rest of this project will hinge on MAP and REDUCE.

One of the things I've had to figure out is quickly finding the index of the *second* appearance of a given item in a list. In normal programming, you can loop through the list, and loop through again for each item. "Looping" isn't a thing in spreadsheets, but you can do something like this instead:


That formula takes the first array, {"A","B","C"}, and for each item, loops through the second array, {"B","C","A"} until there's a match. When a match appears, it returns the letter and the index of the match. So in this case the output would be A3B1C2.

This will ultimately let me check to see if a given point is "behind" another point in the list, and retrieve the index of the point that's "in front". When I go to draw the points on the line later on, I can then reliably replace the points between the "behind" points with the points between the corresponding "in front" points, giving the illusion of opacity in the 3D space.


It's also WAY faster than a QUERY function. I built out a similar formula with queries that worked *technically* on a small line, but immediately broke Google Sheets when I tried inputting anything close to my goal objects.

(I also just realized that I haven't been adding hashtags to any of this, oops. I'll add them at the end of the thread when this is all sorted out and working, so people can find the finished product and not my ramblings. 😅)

Sign in to participate in the conversation
Gosh Darn

A tiny server for experimenting with fun things on the web.