1

When writing data to gsheets with pygsheets - one of my values contains a + char. e.g. +myvalue

When then exporting the data, I get the #NAME? output, instead of the background value.#NAME?, of course the formula bar contains the right value.+myvalue

This is not completely unexpected, however - when use the sheets method and manually import a CSV containing these values - the #NAME? error is not displayed, and instead I can see the +myvalue in the field. (Unless I edit it.)

This is my code for "importing" the csv - of course it's just reading the csv and loading values:

# Authorise with GSheets Service Account
gc = pygsheets.authorize(service_file=service_account_file)

# Open spreadsheet
sh = gc.open_by_key(spreadsheet_key)

# Open Worksheet
#wks = sh.add_worksheet(spreadsheet_hosts_worksheet) # Create Worksheet
wks = sh.worksheet_by_title(worksheet_name)

# Generate list "data" with Values from CSV
with open(inputFile, 'r') as f:
  #reader = csv.reader(f, skipinitialspace=True, delimiter=',', quotechar='"')
  reader = csv.reader(f, delimiter=',', quotechar='"')
  data = list(reader)

# Empty Worksheet
wks.clear()

# Append Values
wks.update_values(crange='A1', values=data)

# Freeze Top Row
wks.frozen_rows=1

Can I change the updating method, so that it takes formulas like text - same way a CSV import function on GSheets would?

My sample data:

['host_name', 'alias', 'address', 'parents', 'use', 'display_name', 'hostgroups', 'contacts', '_ADDINFO', '_SNOWGROUP', '_RTTCRIT', '_RTTWARN', 'contact_groups', 'notes', 'notes_url', 'check_command', 'first_notification_delay', 'check_interval', 'max_check_attempts', 'retry_interval', 'config_filename']
['host', 'destiny islands', '1.1.1.1', 'host.mypalace.com,host.mapalace2.com', 'tmpl_network_device', 'Gingerbread lane', 'hgrp_grandad', '+myvalue', 'ACTION - Don't forget to smile', 'test', '70', '20', '', '', '', '', '', '', '', '', '/folder/filename']
itChi
  • 642
  • 6
  • 19
  • In order to correctly understand your question, can I ask you about it? You want to put the formulas to cells. Is my understanding correct? And what is ``+myvalue``? – Tanaike Apr 05 '19 at 12:32
  • I would like to put the value `+myvalue` into the cells, without it appearing as a formula. So when I view the spreadsheet, the field would say `+myvalue` instead of `#NAME?`. Thank you very much for your time. – itChi Apr 05 '19 at 13:06
  • Thank you for replying. I could understand about your result. So I posted an answer. Could you please confirm it? If that was not the result you want, I apologize. – Tanaike Apr 06 '19 at 00:59

1 Answers1

1

How about using parse=False?

When I saw the script, the default is parse=True. In this case, valueInputOption uses USER_ENTERED. When parse=False is used, valueInputOption uses RAW. By this, +myvalue doesn't #NAME?.

Modified script:

wks.update_values(crange='A1', values=[['+myvalue']])  # ---> #NAME? is put in a cell "A1"

wks.update_values(crange='A2', values=[['+myvalue']], parse=False)  # ---> +myvalue is put in a cell "A2"

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165