
Show HN: A virtual machine made with Google Sheets formulas (no script) - SonOfLilit
In a popular post today[1] the title claims &quot;VM in Google Sheets&quot;, but it&#x27;s actually in Google Script, which is basically Javascript and not an interesting technical accomplishment (definitely a useful educational tool, though).<p>Disappointed, I set out to fix it. Voila!<p><a href="https:&#x2F;&#x2F;docs.google.com&#x2F;spreadsheets&#x2F;d&#x2F;1g3-FZBnkDuLqFYOmk457hOvyrS798vO4X2nV77gVzGI&#x2F;edit" rel="nofollow">https:&#x2F;&#x2F;docs.google.com&#x2F;spreadsheets&#x2F;d&#x2F;1g3-FZBnkDuLqFYOmk457...</a><p>A Brainf<i></i>k interpreter in pure Google Sheets formulas. The hard part was parentheses matching, so I think other VMs would be even easier.<p>[1] <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=14701460" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=14701460</a>
======
briansteffens
Wow this is cool and you did this really fast!

So you did kind of a functional style where each row represents an instruction
being run with a full copy of the state of the VM at that point in time. Then
each subsequent row is based on the state of the previous one. Am I reading
that right? (I'm not familiar with Brainf __k)

~~~
SonOfLilit
Yes. Had to store state somewhere, spreadsheets find "one row per thing" to be
most natural, and in my case the "thing" I'm working with is state, so one row
per state transition it is.

The more interesting hackery is in the parenthesis parser...

~~~
briansteffens
Yeah I don't follow the parenthesis parser. Plan to do a write-up of some kind
on how it works?

~~~
SonOfLilit
The basic algorithm is "parentheses counting": walk the string starting from
an opening bracket, counting +1 per `[` and -1 per `]`; when you reach 0
you're at the matching closing bracket.

First, lets split the code to one char per row:

    
    
        =MID($A$1,D3,1)
    

(D3,D4,... are simply 1,2,3,...)

Now, lets parse `[` as 1, `]` as -1, anything else as 0:

    
    
        =IF(E3="[",1,if(E3="]",-1,0))
    

Now, we will need one column per character in the code, where in the i'th
column the first row is the i'th character's value and each row we go down
accumulates the value of the next character.

    
    
        =G2+OFFSET($F2,G$1,0)
        =G3+OFFSET($F3,G$1,0)
        ...
    

(Column F is the 1,0,-1s. Row 1 is 0,1,2,3..., this time horizontally, which
we use to translate horizontal motion into vertical motion)

Now for the i'th row of the "] match" column, lets find the index of the first
0 in the i'th column, and add `i-1` to get an index from the beginning of the
code instead of from the opening bracket:

    
    
        =MATCH(0,offset($G$3,0,D3-1,18,1),0)+D3-1
        =MATCH(0,offset($G$3,0,D4-1,18,1),0)+D4-1
        ...
    

And all that's left is reverse-search this table to find the matching opening
brackets to every closing bracket, and while we're at it lets clean up all
irrelevant rows to the empty string:

    
    
        =IF(F3=1,C3,IF(F3=-1,MATCH(D3,$B$3:$B$20,0),""))
    

(D3 is the index of the closing bracket, $B$3:$B$20 is interestingly the
column we're currently in - Sheets does not complain as long as there is no
_actual_ circular referencing going on)

Voila! Column B now contains for every bracket its matching bracket's index in
the code, and for anything else, "".

~~~
doubleplusgood
Oh, that's pretty clever. Thanks for the explanation!

------
j45
This is very cool as well.

A comment about how you have presented this - not everything has to be an
interesting technical accomplishment.

Creating beginners is a much harder skill and topic than most realize.

Making topics easy to approach and learn is often much harder than learning
the technical topic.

Impressive how quickly you whipped this up

~~~
SonOfLilit
My previous startup was about teaching programming to beginners with computer
games, so I definitely have an appreciation for that. I made sure to point out
that I still consider Brian's accomplishment very impressive.

It's just that I clicked hoping for one thing and found another, so I felt a
need to show that the second one is also doable :-)

Also, it took me more than I expected. I had to go through a couple of wrong
approaches and some confusion until I figured out bracket matching, and also
had a bug or two that took a while to debug in the interpreter table. These
things aren't hard to make! Try it sometime :-)

~~~
j45
Neat to hear you're in the edtech space as well - I am as well at present.

Would love to learn and chat a bit more about your previous startup (I see you
have posted below and will reply there).

~~~
SonOfLilit
Sure! I emailed you. Or we can discuss it publicly here :-)

------
SonOfLilit
If you still don't get some part of what's going on after copying it and
playing with it a bit (A1 is the code, A2 is the input, you can find an
explanation of BF and a simple Python implementation in my linked slides), try
reading my discussion with briansteffens, who is also the guy who wrote the
Apps Script implementation that inspired this.

------
aviraldg
See also:
[https://www.youtube.com/watch?v=uNjxe8ShM-8](https://www.youtube.com/watch?v=uNjxe8ShM-8)
(On The Turing Completeness of PowerPoint)

------
artursapek
This is why I come to HN.

~~~
SonOfLilit
Wait, did you use to work for Codecademy? Can we chat a while? As a former
competitor, I am really curious why you did some stuff this way and not that,
and would really like to be able to recommend you to all the people asking me
how to learn programming now that I don't actively teach anymore.

~~~
sattoshi
This probably wasn't why he came to HN..

~~~
_ao789
Lol, truestory

------
myhf
It helps to visualize the machine activity if you use conditional formatting
to show the cursor location:

[https://docs.google.com/spreadsheets/d/1ZJECuw6GXA_wEMnfe2Oj...](https://docs.google.com/spreadsheets/d/1ZJECuw6GXA_wEMnfe2OjOD0taVQTU0rTKY8PvfHQAtU/edit?usp=sharing)

------
thriftwy
Next step is emulating a more fulfilling instruction set using formulas only?

~~~
SonOfLilit
Not sure what you mean by "more fulfilling", but I don't think any new
techniques would be required. I encourage you to try! Maybe build a Forth?
That would be extremely cool!

------
kronos29296
You made my day. Though an explanation would help understanding it. For the
commonfolk like me.

~~~
eatonphil
Brainfuck is a stack-based language with only 6-8? syntactic elements for
manipulating the stack. + increments the current stack element, - decrements
it. > increases the stack pointer and < decreases it. [ and ] have to with
conditional jumping I believe. And it is common to convert the elements in the
stack to ascii characters to display a result.

So if you go to the orange section of the sheet you see where the program
state is displayed. Each row represents the state of the program at that
point. The "Out" column is the stack represented as ASCII characters. The
"Cursor" column is the stack pointer. The "IP" column is the instruction
pointer (which instruction you're currently looking at). The "Instruction"
column is the instruction being done at that point in time. The "Cells" column
is the stack displayed from left to right.

I may be a little off but that is the general idea.

~~~
SonOfLilit
You got it basically right, a few small corrections:

Replace the word "stack" with "memory buffer" or "tape" everywhere. "Out"
represents STDOUT, there is the "." command for printing (and "," for reading
from STDIN).

Thanks!

~~~
eatonphil
Ah. I thought "." must be printing (as it's something similar to that in Forth
[guess it's ".s" though not "."]), but I was confused by the comma because I
didn't understand how you'd be reading from stdin here. Actually I'm still
confused. What are you reading in?

~~~
fasquoika
`.` in Forth is to pop a number and print it, `.s` is to print the whole stack
non-destructively

------
GrumpyNl
Welcome back to 1985

