Escaping cell values starting with =

[Baraksi]

Setting a value starting with '=' using cell.value setter is treated as a 'formulaValue' valueType. Escaping it by myself by adding a single apostrophe is enforcing it to being treated as a 'stringValue' but than the outcome is a double single-apostrophe in the google sheet cell, since the 'stringValue' valueType is auto escaped (seems like google sheets API does it, which is good).

I tried to re-set the valueType to 'cell.userEnteredFormat.numberFormat = { type: 'TEXT' }', right after setting the new value which starts with '=', so it will be auto escaped too. The problem is that '_getUpdateRequest' function is using 'this._draftData.valueType' for the request.

A workaround I'm currently using to overcome this issue, just in case it helps someone:

cell.value = textValue
cell._draftData.valueType = 'stringValue' // re-setting valueType

[theoephraim]

What do you think desired behaviour would be - ie how do you want to set the cell value. Probable by escaping with a single apostrophe like the UI on google sheets - ex: cell.value = "'= not a formula"

In this case I just need to make sure to treat it as a stringValue but remove the apostrophe before sending it?

[Baraksi]

Makes sense, it's closer to how google sheets act and aligned with the auto detection rules the module already follows when updating cell values. Moreover, this is the escaping I used when added new rows using worksheet.addRows function. Worth mentioning about worksheet.addRows function, that values with '+' prefix must be escaped too when using it, but there is no need to escape this prefix when using a cell update since it's already being detected as a string value. This behavior leads to some inconsistency by using different escape method for each case (insert / update), from a code point of view.

I must admit I still wonder about the other option of keeping the auto detection as it is and allowing the flexibility of re-setting the valueType.

Both equally makes sense to me.

[jakubriegel]

Any updates here?

[theoephraim]

@Baraksi @jakubriegel - the lastest version has some typed setters/getters, so you can explicitly say cell.stringValue = '=LooksLikeAFormula' and it will escape it properly. Please try it out and let me know if it all makes sense!

The rows-based api will still behave like you typed it into a cell in the UI, as that's what the values apis do...