Google API: Sheets updater saves values as strings

[mwiegand]

Hi,

I’ve uploaded data into Google Sheets via the Google Sheets Updater and noticed values like doubles are inserted with a trailing ’ rendering the data i.e. in a pivot unusable. Any idea how to not append a trailing ’ ?

Thanks a lot
Mike

[marten_kose]

Hi Mike,

As a quick workaround you could format the values as numbers in Google Sheets, so you will be able to perform aggregations on them. However, I’d expect the values to be formatted as number automatically (and come without a single quotation mark), when the KNIME datatype was int, double or long.

I’ll open an issue in our bug tracking system. Thanks for the feedback.

[mwiegand]

Unfortunately that won’t work as number prefixed by ’ can#t be converted. I even integrated a Google Sheets script to search & replace the character but this takes too long and only works upon certain circumstance.

[marten_kose]

That is strange, because the workaround works perfectly fine for me. If I select a value like this '123 and format it as number in Google Sheets, the prefixed ’ are gone, the data is formated like this 123.00 and I can perform aggregations. Am I missing something?

[oole]

Hi @mw

It seems to be a problem with the option that is used to actually write the values into Google Sheets.

There are two options to write to Google Sheets (RAW and USER_ENTERED). RAW is the default and seems to cause the leading ’ problem.

The setting is not exposed in the node dialog, but you can change it using flow variables.

Here is an example workflow, that might solve the problem: google_sheets_change_variable_setting.knwf (12.6 KB)

[mwiegand]

Hi @oole,

that’s marvelous. Much appreciate your solution as it saves a ton of work
Got to pay more attentions to the flow variable. Though, I must admit, their naming is very cryptic sometimes not allowing to draw conclusion to i.e. the associated filed / funciton in the note itself. Do you know any documentation that could be of help?

Cheers
Mike