search

jpillora / node-edit-google-spreadsheet

A simple API for editing Google Spreadsheets
304 stars 101 forks source link

{getValues:true} not evaluating REGEXREPLACE #71

Closed humantorch closed 9 years ago

humantorch commented 9 years ago

I've got a number of cells in my sheet that use REGEXREPLACE(...) to create their contents from a combination of other cells (and replacing spaces with - characters, making everything lower-case, etc.), and those regexes aren't being evaluated during export even if {getValues:true} is set. Is this expected behavior or is there another option that I'm missing? These cells export just fine with a manual File > Download as... > CSV from within the Google doc itself, but I'd really like to automate this process. Any ideas on how I could pull this off?

jpillora commented 9 years ago

Add a console.log(cell) here https://github.com/jpillora/node-edit-google-spreadsheet/blob/stable/lib/util.js#L31 when you pull the value of a REGEXREPLACE, the cell might contain a property that I haven't come across yet

humantorch commented 9 years ago

Hm, interesting. An example of the console.log(cell) output from a cell with the REGEXREPLACE() in it is:

{ row: 7, col: 1, inputValue: '=REGEXREPLACE(REGEXREPLACE(LOWER(R6C1&" "&R[0]C[1]),"[\\s-]+","-"), "[^A-Za-z0-9-]", "")', '$t': 'intro-sequence--loader-sub-headline' }

It was that $t value that I wanted here, not the inputValue, so I realized that I was actually getting the correct response back. Digging further into this I finally figured out that setting getValues:true alone wasn't going to get me where I needed to go, I also needed to ensure that useCellTextValues: true was set as well (it was set to false in an earlier version of the project I'm working on).

Anyways, all of this is to say that it's sorted now and was pretty much entirely a PEBCAK error. Thanks for your response, I'd have spent far too long digging through this otherwise!