Skip to content

Home

Jump to bottom Edit New page
Chao-Kuei Hung edited this page Dec 2, 2016 · 29 revisions

EtherCalc wiki

EtherCalc is the spreadsheet equivalent of a wiki document, without logins and without editing locks. People can collaborate on a spreadsheet-like page simultaneously. It's somewhat a google sheet alternative, open source style.

Read french version here.

Use Cases

When or why would you use EtherCalc? Here are a few scenarios.

  • Scheduling a meeting without asking via e-mails nor filling one-time forms: Every participant can see the most up-to-date availability of other members. One can update his/her schedule any number of times. The number of available participants in each time slot can be easily seen.
  • Car pooling arrangement: Who will ride in whose car? How many seats are still available in each car?
  • Students mutual-grading: What does each student think of each other's exercises or projects?
  • A good communication tool between the Direction of Programs and the Studio in a radio station : the director writes the roadmap in a calc sheet and the technician at the studio can easily follow along and receive the latest modifications without any interaction needed (no required clicks, no required refresh).
  • ...

Syntax

As a spreadsheet, ethercalc accepts the following in a cell:

  • mathematical formulae or other functions beginning with the equal sign =, e.g. =sqrt(3)/2, =if(1+1<0, 'surprise!', 'ok'), ... See OpenFormula and the SocialCalc.Formula.FunctionList variable in the source code for the complete list of formulas it supports. Certain functions (such as DAVERAGE, DCOUNT, DMAX, DSUM, COUNTIF, SUMIF, ...) require a criteria parameter. The criteria parameter can be either a number (possibly written as a string prefixed by one of the comparison operators <, <=, =, >=, >, <>) or a textual string. A textual string criteria containing either * or ? will be interpreted as an excel wildcard string or as a javascript regular expression.
  • date of the form (by default) mm/dd/yyyy where yyyy>=1900, e.g. 12/25/2016
  • texts beginning with the single quote character ', e.g. 'hello world

For text cells, ethercalc supports the following formatting directives in its "default" formatting option:

  • bold: the point is *collaboration*
  • italic: the point is _collaboration_
  • strikethrough: can I -haz- cheezburger
  • teletype: it reads ``SOS`` (backquote character)
  • horizontal line: -- (two or more dashes at the very beginning of the cell's text)
  • header text of various levels: ^^Section 4 (one or more caret characters at the very beginning of the cell's text)
  • hyperlink: Leonard Nimoy as "Spock"<https://en.wikipedia.org/wiki/Spock>

For more advanced editing, please see the parser, which is modeled after Socialtext Syntax.

For even richer formatting options such as color text/background, you can select a range of cells, switch to the format tab, and change format:text from "default" to "html". Then you can type html syntax such as <span style="color:red; background:yellow">hello</span> in your cells. See e.g.: http://www.w3schools.com/html/html_formatting.asp

Tips and tricks

Use a cell value from another sheet.

Example, I have two sheets named sheet1 and sheet2 and I want to display cell A1's value from sheet1 in the sheet2's cell B3.

  1. Go to cell B3 in sheet2
  2. type this formula:="sheet1"!A1

Color a cell depending of its value (aka Conditional Formating) .

Example, I want a cell B2 to turn green if its value is foo; or else to be red.

  1. Go to cell B2
  2. In the menu/format/format/text choose "HTML" and save for this cell
  3. Then type this formula: =if(A1="foo","<span style=""background-color:rgb(81,184,72);color:rgb(81,184,72)"">_______</span>","<span style=""background-color:rgb(226,86,43);color:rgb(226,86,43)"">_______</span>")

So how does it work? Your IF function checks A1's value and gives you two possible output each of them being a simple text string made of underscores. The only subtility here is that we specify both pen color and background color to be the same (either green or red) so as it looks like the cell is filled either in red or in green. This is a workaround since there is no current straitforward function to set an empty cell's background color.

Create a form

Say you have a calc named foo, which URL is https://calc.example.org/foo.

You can create a form by adding /appeditor to your URL (which become https://calc.example.org/foo/appeditor).

You can add '/form' to show the form - e.g. https://calc.example.org/foo/form

You screen will be split in three parts. To the left, the form as it will be rendered, to the right, your calc, which will be used to create the form, and at the bottom, the results from the form.

To create an input field, go in a cell and write =TEXTBOX("some text"). Note that you can't use =TEXTBOX(""), you have to put the quotes, but the content can be blank.

Have a look at the other form possibilities by clicking on the "Fx" button on the spreadsheet, and selecting "Button & Input" section.

To create a submit button, go in a cell and write =submit("send") (you can put any text you want).

To get the form link to share, click on the "Form" tab of your calc, then on the "Live form" button. It will redirect you to your form: you only have to share that page's URL.

To get the results of your form, add _formdata/view at the end of your calc's URL (which become https://calc.example.org/foo_formdata/view).

Warning! It's easy to get the results page's URL or the original calc's URL from the form URL. Don't use it to get private datas!

Warning! (part 2) There's currently an issue with forms on Ethercalc: the input fields lose focus after each keystroke.

Summary:

Export

You can export your calc in CSV or XLSX format by appending .csv ou .xlsx at the end of your calc's URL (https://calc.example.org/foo.csv).

Links to More Tips

Please also see the ethercalc Q&A for Stack Exchange Web Applications

Add a custom footer
Add a custom sidebar
Clone this wiki locally