Spreadsheet cells in JavaScript for Google Docs - Sketch-n-Script
2020/03/13 — Mikaël Mayer - updated from my github blog post.
Have you ever wished you could synchronize content within one Google doc? Do you need temporary placeholders that you will replace later? Do you want to generate text? Do you miss spreadsheets in docs?
If you answered yes to one of these questions, this Google docs add-on this for you.
It enables you to define names (a.k.a. variables) anywhere in the document, and to insert computations that produce rich text anywhere in the document.
Define names
You can define a name anywhere in your document. It has to start with a word, the sign equal '=' and a non-empty space behind it. If there is an opening parenthesis, the content will be evaluated as JavaScript. If there is no opening parenthesis, the remaining until the end of the newline is treated as raw text. Definitions can depend on previous definitions.
The following are all valid definitions of names:
version = 1.0 beta
name = ("MySoft" + version)
citations = ({ mayer18: "https://dl.acm.org/citation.cfm?id=3276497", chugh16: "https://dl.acm.org/citation.cfm?id=2908103" })
cite = (function (name) { return typeof citations[name] !== "undefined" ? ["[" + name + "]", {link: citations[name]}] : "[" + name + "???]"; })
Write formulas in your document
Anywhere in the document, write the sign = followed by either
- a name, e.g.
=version - a name followed by a JavaScript expression in parentheses, e.g.
=email("a@b.com", "Hello") - a JavaScript expression inside parentheses or square brackets, e.g.
=["p", {}, ["This is a paragraph"]]
=("Hello" + "world") - a name followed by the sign @ and a javascript expression, to give a name to the expression, e.g.
=name@("Hello")
For example, in a document, you would write raw
=name is out!
=[name, {bold: true}] is a n=(function() { var x = ""; var i = 10; while(i-- > 0) x = x + "i"; return x} ())ce =(/*s=*/"software") based on bidirectional evaluation =cite("mayer18") that goes beyond the original ideas of prodirect manipulation =s =cite("chugh16")
Render formulas
Open the menu "Formulas" and click on "Evaluate formulas". It replaces all formulas by their values. If you selected some text, only formulas in the selection would be evaluated. The document above would be rendered as:
MySoft 1.0 beta is out!
MySoft 1.0 beta is a niiiiiiiiiice software based on bidirectional evaluation [mayer18] that goes beyond the original ideas of prodirect manipulation software [chugh16]
API available
Inside paragraphs
Formulas used inside paragraphs in the document should either return raw text, or a 2-element array where the first element is the text, and the second is a Javascript object containing styling material. If the method setThing exists in this page, then thing : value is a valid style attribute. Here is a list of supported attributes and shortcuts
backgroundColor(orbackground): a string representing a color like"#FF00FF".bold: boolean (trueorfalse).fontFamily: string representing a valid font name.fontSize: integer representing a valid font size.foregroundColor(orcolor): a string representing a color like"#FF00FF".italic: boolean (trueorfalse).linkUrl(orlink): string representing an URL.strikeThrough(orstrike): boolean (trueorfalse).textAlignment(oralign): either"normal","superscript"or"subscript"underline: boolean (trueorfalse).
Stand-alone formulas
It is also possible to have formulas that generate paragraphs, list items, headings and tables. In this case, the formula should span an entire paragraph. Here are the available functions:
- Paragraphs, e.g.
=["p", {}, ["This is a paragraph"]] - Titles, e.g.
=["p", {heading: "heading1"}, ["Hello ", ["world", {italic:true}]]]
=h2("Hello world") - Tables, e.g.
=["table", {}, [["x", "1", "2"], ["1", "1", "2"], ["2", "2", "4"]]] - List items, e.g.
=["li", {id: 1, nesting: 0, glyph: "number"}, "Todo item"]
=li("Todo item")
You can always consult the cheat sheet which is at the bottom of the extension.
Visualize where formulas are being used
To make sure a field is computed and not just text, click on "highlight values" and then click on "Display values". It highlights all places where the text is computed. If you selected some text, only computed text in the selection would be highlighted. The document above would be transformed to (colors should be background colors, but I cannot do this in WordPress):
MySoft 1.0 beta is out!
MySoft 1.0 beta is a niiiiiiiiiice software based on bidirectional evaluation [mayer18] that goes beyond the original ideas of prodirect manipulation software [chugh16]
You can hide these highlights by unchecking the box "Hightlight values" and click on "Display values". If you selected some text, only highlights in the selection would be removed.
Reveal formulas
It can be useful to change formulas. To do so, check "highlight formulas" and click on "Display formulas" . It will replace all computed fields by their respective formulas and highlight them in orange. If you selected a portion of the text, only the formulas in the selection would be revealed. You can then edit any formula:
=name is out!
=name is a n=(function() { var x = ""; var i = 10; while(i-- > 0) x = x + "i"; return x} ())ce =(/*s=*/"software") based on bidirectional evaluation =cite("mayer18") that goes beyond the original ideas of prodirect manipulation =s =cite("chugh16")
Once you're done, click on "Formulas" and then "Display values" to replace the formulas by their values.
=name is out!
MySoft 1.0 is a niiiiiiiiiice software based on bidirectional evaluation [mayer18] that goes beyond the original ideas of prodirect manipulation software [chugh16]
Availability
This extension is available as a Google docs add-on that can be added to any document by opening it, open "Add-ons" and click "Add add-on", and look for "Formulas for Google Docs".
Wish list
There are many features one would like from this extension:
- Modify a formula or a name's value by modifying its output anywhere. It works for variables, paragraphs, tables, list items, and text constant only for now. One day, it will work even for functions.
- Reading data from spreadsheet or other APIs
- Keyboard shortcuts
They might be coming soon. If you want them sooner,
Copy-and paste unfortunately cannot be supported due to how formulas are stored. However, you can reveal a particular formula to duplicate it.
This work was supported by Swiss National Science Foundation Early Postdoc.Mobility Fellowship No. 175041
---------------------------
Privacy Policy
Mikaël Mayer built the Spreadsheet cells in JavaScript for Google Docs - Sketch-n-Script app as an Open Source app. This SERVICE is provided by Mikaël Mayer at no cost and is intended for use as is.
This page is used to inform visitors regarding my policies with the collection, use, and disclosure of Personal Information if anyone decided to use my Service.
If you choose to use my Service, then you agree to the collection and use of information in relation to this policy. I am not collecting personal information in any way, except if you click on the survey and fill out information to contact me.
The terms used in this Privacy Policy have the same meanings as in our Terms and Conditions, which is accessible at Spreadsheet cells in JavaScript for Google Docs - Sketch-n-Script unless otherwise defined in this Privacy Policy.
Information Collection and Use
While using our Service, I will not require you to provide us with certain personally identifiable information. The information that is collected from the document will be retained on your document and is not collected by me in any way.
The app does not use third party services that may collect information used to identify you.
Log Data
I want to inform you that whenever you use my Service, in a case of an error in the app, I do not collect data and information. Submitting error is a voluntary step you can do yourself at this link.
Cookies
Cookies are files with a small amount of data that are commonly used as anonymous unique identifiers. These are sent to your browser from the websites that you visit and are stored on your device's internal memory.
This Service does not use these “cookies”.
Service Providers
I may employ third-party companies and individuals due to the following reasons:
- To facilitate our Service;
- To provide the Service on our behalf;
- To perform Service-related services; or
- To assist us in analyzing how our Service is used.
I want to inform users of this Service that these third parties do not have access to your Personal Information.
Security
I value your trust in installing the App. But remember that no software is 100% safe, use it at your own risks and make back-ups regularly to avoid any error.
Links to Other Sites
This Service may contain links to other sites. If you click on a third-party link, you will be directed to that site. Note that these external sites are not operated by me. Therefore, I strongly advise you to review the Privacy Policy of these websites. I have no control over and assume no responsibility for the content, privacy policies, or practices of any third-party sites or services.
Children’s Privacy
These Services do not address anyone under the age of 13. I do not knowingly collect personally identifiable information from children under 13. In the case I discover that a child under 13 has provided me with personal information, I immediately delete this from our servers. If you are a parent or guardian and you are aware that your child has provided us with personal information, please contact me so that I will be able to do necessary actions.
Changes to This Privacy Policy
I may update our Privacy Policy from time to time. Thus, you are advised to review this page periodically for any changes. I will notify you of any changes by posting the new Privacy Policy on this page. These changes are effective immediately after they are posted on this page.
Authorizations required
Just a comment on the authorizations this extension requires, to ensure it conforms to Google docs Policy. This extension requires two sensitive authorizations.
- Permission to access the UI of Google docs
- Permission to communicate with an external server.
The first permission is straightforward, since the extension's purpose is to display a sidebar to trigger the execution of JavaScript formulas, and switch from/to formula to/from value display.
The second permission is more subtle. Fortunately, the project is open-source , so you can inspect that the only place when invoke UrlFetchApp, it's on Code.js line 1287. The variable url there is actually provided by users of the extension themselves, and is in no way invoked by the extension in a way to monitor your usage.
Being able to fetch URL means that it makes it possible to
1) Generate images from URLs, e.g. LaTeX formulas as highlighted in the example.
2) Fetch custom content within your Google doc. For example, if you write the following in your Google doc
test = (UrlFetchApp.fetch(“http://
www.mocky.io/v2/ 5ca3eb514b00005600209882”). getContentText()) =test
After execution, you'll see "Hello world", which is the text that I saved in mocky.io at this particular URL. You can use your own.
How does this application accesses, uses, stores, or shares Google user data?
This application does not access your Google user data. Authentication is only required to access the app, but the app is open-source, you can check it here and by this post I claim that this is the exact source code.
The only data this application stores is by using the APIs DocumentProperties, UserProperties and ScriptProperties to store formulas and reminders to fill the evaluation on Google form if you did not discard them.
Contact Us
If you have any questions or suggestions about my Privacy Policy, do not hesitate to contact me at sketch-n-sketch@googlegroups.com.