Sheet Block Editor

Sheet Block Editor is a block-based visual programming editor for Google Sheets. The editor can read a text formula and represent its parts as building blocks. These blocks can be edited and reconfigured then writen back out as text for use in a Google Sheet. Formulas can also be made with blocks from scratch. Try it out!

For more detailed instructions on how to use it, see the Basic Usage on the wiki.

Currently the editor is in an experimental state. The editor exists on its own webpage to be used alongside Google Sheets via copying and pasting. Adapting the editor to be an extension for Google Sheets is possible but not yet planned. Your enthusiastic support makes that a more likely reality. Additionally, there are some functionality limitations. See the list of known feature gaps.

If you encounter bugs or would like to suggest an improvement please create an issue.

Development

Setup

1. Install node modules npm i
2. Start the webserver npm start. Open http://localhost:3000/

Source Tour

(generated with tree --gitignore --noreport -d -I node_modules and annotated)

.
└── src
    ├── appscript: code specific to google appscript implementation
    ├── blockSheets: defines the block language and related behaviors
    │   ├── codeGenerators: modules that generate code/notation from blocks
    │   │   ├── googleSheetsFormula
    │   │   └── latex
    │   ├── generatedBlocks: support for generating function block defs and generators from the condensed format
    │   │   └── generated: function information in a condensed format (commited but generated by generateBlockDef)
    │   ├── parsing: turn formula text into blocks
    │   └── staticBlocks: block definitions that are not generated
    │       ├── Logical
    │       ├── Math
    │       ├── Text
    │       ├── Values
    │       └── demo
    ├── generateBlockDef: python script for formatting function info into an intermediate format
    └── webapp: standalone web app

Targets

There are two main build targets, the Github pages site and appscript bundle used in Google sheets. The appscript frontend assets are hostend on the github pages site.

Wepack configs:

• webpack.common.js - elements common to all targets (some overrides)
• webpack.pages.js (npm run build-pages) - the github pages site.
• webpack.appscript.js (npm run build-appscript) - the appscript bundle (also builds pages somewhat unnecessarily).
• webpack.dev.js (npm run start) - builds github pages, appscript bundle and a test page to simulate the appscript frontend integration.

Build directory tour:

build
├── appscript
│   ├── index.html (dev, appscript) - deployed to appscript bundle
│   ├── appscript.gs (appscript) - appscript for controlling the modal integration
│   └── test.html (dev) - simulates the modal integration
├── appscript.js - entry point for the appscript page, hosted on github pages
├── blockSheets.js - blocks, parsers, and blockly config for sheet blocks.
├── blockly.js - blockly library
├── index.html - github pages home page
├── index.js - github pages entry point
├── media/ - required by blockly
├── runtime.js - webpack runtime
└── style.css

Publishing GH Pages Site

1. Checkout and pull main
2. Run tests npm run test
3. Clean the build directory rm -r build
4. Build and deploy with npm run pages

Publishing Appscript

(requires access to the appscript project)

1. Checkout and pull main
2. Run tests npm run test
3. Clean the build directory rm -r build
4. Build npm run build-appscript
5. Copy files to the GAS Sheet Block Editor project
   • build/appscript/sheetsMain.gs to sheetMain.gs
   • build/appscript/index.html to block.html
6. Execute the Test Deployment from the GAS project to use in the test file

Other Generated

• Generate block definitions: (cd src/generateBlockDef && python3 generate.py --all)
• Generate src/blockSheets/parsing/formula.js npm run parser

FAQ

Why do I get "TypeError: Converting circular structure to JSON" when running tests?

TypeError: Converting circular structure to JSON
        --> starting at object with constructor 'Connection$$module$build$src$core$connection'
        |     property 'targetConnection' -> object with constructor 'Connection$$module$build$src$core$connection'
        --- property 'targetConnection' closes the circle
        at stringify (<anonymous>)

This error usually shows up when too many arguments or invalid arguments have been applied to a block being instantiated by JSON. Block children reference their parents, creating a circular reference. For some reason argument error triggers this structure to be turned into json and that blows up all the tests. Isolate with skips and fix the arguments.