如何使用PYTHON的gspend wks.update_cell更新Google电子表格中的单元格 [英] How to update cells in a google spreadsheet with python’s gspread wks.update_cells

问题描述

我在GS中有一个文档，其中自动上载了字符串格式的数值数据。 在所有单元格中都有一个标记，因此我可以通过删除它将字符串转换为数字。

当我使用gspend方法更新一个单元格时：wks.update_acell("B7", str(1939.0).replace("'"," "))

但我想使用wks.update_cell更改区域中的所有单元格。

在官方gspend文档中有一个例子：

cell_list = wks.range('A1:C7')

for cell in cell_list:
    cell.value = 'O_o'

# Update in batch
wks.update_cells(cell_list)

在此表单中，它可以工作，但在我使用cell.value = str.replace("'","")的情况下，我收到错误：

TypeError: replace() takes at least 2 arguments (1 given)

我认为我需要将…放在括号str()中。替换-但不理解有人能帮帮我吗？

PS 完整代码：

import pandas as pd
import numpy as np
from pandas import Series,DataFrame
from file4dir1 import rep


    pd.set_option('display.max_columns',None)
    pd.set_option('display.expand_frame_repr',False)
    pd.set_option('max_colwidth',-1)

    mytoken='***'
    project = '***'

    DateFrom="2019-12-01"
    DateTo="2019-12-31"

    data=rep(mytoken,project,DateFrom,DateTo)

    file=open("cashe3.csv","w")
    file.write(data)
    file.close()
    f=pd.read_csv("cashe3.csv", header=1, sep=' ',index_col=0,parse_dates=True)

import googleapiclient.discovery
import argparse
from apiclient import discovery
from oauth2client import client
from oauth2client import tools  

    SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
    CLIENT_SECRET_FILE = "myGSapp4.json"
    APPLICATION_NAME = 'Google Sheets API Report'
    credential_path = 'sheets.googleapis.com-report.json'

from httplib2 import Http
from oauth2client.file import Storage   

    store = Storage(credential_path)
    credentials = store.get()
    parser = argparse.ArgumentParser(formatter_class=argparse.RawDescriptionHelpFormatter, parents=[tools.argparser])
    flags = parser.parse_args([])
    if not credentials or credentials.invalid:
        flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
        flow.user_agent = APPLICATION_NAME
        if flags:
            credentials = tools.run_flow(flow, store, flags)
        print('Storing credentials to ' + credential_path)

from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials
from apiclient.discovery import build
from httplib2 import Http


import gspread
from df2gspread import df2gspread as d2g     

    spreadsheet_key = '***'
    wks_name = 'Sheet0'
    d2g.upload(f, spreadsheet_key, wks_name, credentials=credentials, row_names=True)

#仅举例说明行和列索引

    gc = gspread.authorize(credentials)
    wks_name = gc.open_by_key('***').get_worksheet(0)
    cell_list = wks_name.range('E7:E13')
    requests = {
        "requests": [
            {
                "findReplace": {
                    "range": {
                        "sheetId": 'Sheet0',
                        "startRowIndex": 7,
                        "endRowIndex": 13,
                        "startColumnIndex": 5,
                        "endColumnIndex": 5
                    },
                    "find": "^'",
                    "searchByRegex": True,
                    "includeFormulas": True,
                    "replacement": ""
                }
            }
        ]
    }

    wks_name.update_cells(cell_list)

推荐答案

试一试，应该行得通：

cell_list = wks.range('A1:C7')

for cell in cell_list:
    cell.value = 'O_o'

# Update in batch
wks.update_cells(cell_list, value_input_option='USER_ENTERED')

您缺少的是：value_input_option='USER_ENTERED'

如有任何问题，请查看 API Reference

这篇关于如何使用PYTHON的gspend wks.update_cell更新Google电子表格中的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！