excel4node icon indicating copy to clipboard operation
excel4node copied to clipboard

Formula Parse Error in google sheets

Open neerajhiretual opened this issue 5 years ago • 2 comments

Describe the bug Formula Parse Error in google sheets. I am trying to add a sum formula in a cell. But google sheets show "Formular Parse Error". However, MS Excel and other Excel clients work as expected. Another weird thing is, if i create a excel sheet manually from MS Excel and add a formula in that to sum the cells, and upload to view in Google Sheets. That file's formula is calculated correctly by Google Sheets. I am not sure if anyone has encountered this issue before. Looks like there is an extra "=" appended by the Google Sheet. But Google Sheet will not add extra "=" if i create a file from MS Excel and add the formula.

image

To Reproduce Link for gist that will replicate issue here.

  • Run the gist locally and create the file in question.
  • Send the file to yourself in gmail OR upload to your GoogleDrive.
  • Open using Google Sheets to see the error in the cell with the formula.

Expected behavior Formula generated in excel should be calculated correctly.

Environment:

  • OS: [Ubuntu 18.04]
  • Node Version: [8.11.3]
  • excel4node Version: [1.7.2]
  • Application: [Google Sheets]

neerajhiretual avatar Sep 16 '19 23:09 neerajhiretual

Here's a free google-sheet formula parse and evaluate tool for untangling formulae:

https://docs.google.com/spreadsheets/d/1wwclmAMXGaFanVLlyvzv63fDx6JkwOcT6Dkisac-sXI/copy

It's free. It's tiny. It's just a simple shared google-sheet file (with no macros, add-ons or fancy stuff). Click the link to make your own copy. Have a try, and if you like it, bookmark it for future use while fighting formulae in google-sheet.

I made it to make up for the lack in google sheet of the Evaluate Formula (F9) parser native to Excel. It analyses any formula pasted into yellow cell G1, splitting and colouring them by depth or by chosen characters, and referencing them against the available formulae to see where brackets or commas are likely to have been omitted or put in the wrong place. A handy tool for simple checks, and for making or parsing complex or nested formulas, and for finding 'Parse Error', and diagnosing #REF!, #NAME?, #N/A, #NUM!, #VALUE! error codes.

Enjoy!

thegurumonkey avatar Jun 01 '20 14:06 thegurumonkey

hi @neerajhiretual . did you solved it? i've the same issue thanks!

xjuni0r87 avatar Jul 06 '20 13:07 xjuni0r87