Formula support

[jennybc]

I'm not sure if or when we want to handle this. We're clearly not doing so right now. But you can get and edit formulas via the API. Right now, we seem to assume "contents is contents".

From the API docs on the cells feed:

The inputValue attribute of a cell entry always contains the value that a user would otherwise type into the Google Sheets user interface to manipulate the cell (i.e. either a literal value or a formula). To set a formula or a literal value on a cell, provide the text of the formula or value as the inputValue attribute. Remember that formulas must start with = to be considered a formula. If a cell contains a formula, the formula is always provided as the inputValue when cell entries are returned by the API.

The numericValue attribute of a cell entry, when present, indicates that the cell was determined to have a numeric value, and its numeric value is indicated with this attributed.

The literal value of the cell element is the calculated value of the cell, without formatting applied. If the cell contains a formula, the calculated value is given here.

[jennybc]

from twitter: "Is there any way to import formulas (specifically HYPERLINK) along with content?"

[jrosen48]

Thanks for adding my question on Twitter. I'm sorry for not checking here, first.

I asked a Stack Overflow question about this here.

I think this would be a useful feature. After reading the excerpt from the API docs on the cells feed, does it sound like you can extract a formula from a cell, or enter the inputValue?

I ran into an issue when simply trying to extract a formula from a cell (instead of the literal value). I had to use a Google Apps Script function to do so:

function myFunction() {
  var formulas = SpreadsheetApp.getActiveRange().getFormulas();
  var toPut = SpreadsheetApp.getActiveRange().offset(0, 1, SpreadsheetApp.getActiveRange().getNumRows(), 1);
  var extracted = [];
  for(var index in formulas){
    var array = formulas[index];
    for(var formulaIndex in array){
      Logger.log("f:" + array[formulaIndex]);
  extracted.push([array[formulaIndex].substring(array[formulaIndex].indexOf('"')+1, array[formulaIndex].lastIndexOf('"'))]);
}
  }

  toPut.setValues(extracted);
}

function onOpen(e){

    SpreadsheetApp.getUi().createMenu("Testing").addItem("myFunc", 'myFunction').addToUi();
}

[jennybc]

Hi @jrosen48

I am on vacation right now with poor / nonexistent internet access. But I will work on this when i get back in ~1.5 weeks.

Yes I can definitely access the formulas. Here's where I've made some previous notes to myself to come back to this:

googlesheets/R/gs_read_cellfeed.R

Lines 123 to 128 in 97db8cc

	# see issue #19 about all the places cell data is (mostly redundantly)
	# stored in the XML, such as: content_text = x$content$text,
	# cell_inputValue = x$cell$.attrs["inputValue"], cell_numericValue =
	# x$cell$.attrs["numericValue"], when/if we think about formulas
	# explicitly, we will want to come back and distinguish between inputValue
	# and numericValue

Now that I've found someone who cares, it seems worth doing!

[jrosen48]

Thank you @jennybc, that sounds great!

[jennybc]

Just coming back to this @jrosen48 and have already put my hands on the formula. So don't despair. Tackling this will solve another issue (#152) as well.

[jrosen48]

Great!

[jennybc]

Merged into #152 now. Closing.