用公式字符写入gsheets的pygsheets数据将数据作为公式 [英] pygsheets data written to gsheets with formulaic chars takes data as formulas

问题描述

使用pygsheets将数据写入gsheets时-我的值之一包含+字符.例如+myvalue

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

然后导出数据时，我得到的是#NAME?输出，而不是背景值.，当然，编辑栏包含正确的值.

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

这并不是完全出乎意料，但是-使用工作表方法并手动导入包含这些值的CSV时-不会显示#NAME?错误，而是可以在该字段中看到+ 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.)

这是我用于导入" csv的代码-当然，它只是读取csv并加载值:

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

我可以更改更新方法，使其采用类似于文本的公式-与GSheets上的CSV import函数一样吗?

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

我的样本数据:

['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']

推荐答案

如何使用parse=False?

当我看到脚本时， ，默认值为parse=True.在这种情况下，valueInputOption使用USER_ENTERED.使用parse=False时，valueInputOption使用RAW.这样，+myvalue不会#NAME?.

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?.

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"

参考文献:

• pygsheets.sheet的源代码
• ValueInputOption

References:

• Source code for pygsheets.sheet
• ValueInputOption

这篇关于用公式字符写入gsheets的pygsheets数据将数据作为公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！