时间戳

首页 > 解决方案 > Google 工作表的“value_input_option”不会删除单元格中的撇号

python - Google 工作表的“value_input_option”不会删除单元格中的撇号

问题描述

我一直在从事一个需要数据抓取并将数据发送到谷歌表格的项目。但是，发送的记录的开头有一个撇号，如下面的示例，这是它在 google 表格中的外观。

这是带有撇号的日期值

这是带有撇号的日期值

这是带有撇号的数值

这是带有撇号的数值

我曾经value_input_option = 'USER_ENTERED'尝试摆脱撇号，但无法让它发挥作用。下面是我的代码示例。

import requests
from bs4 import BeautifulSoup
import time
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from google.oauth2.service_account import Credentials
import pandas as pd
index = 818
url = ["https://www.investing.com/indices/us-spx-500-historical-data", 'https://www.investing.com/commodities/gold-historical-data', 'https://www.investing.com/indices/investing.com-btc-eur-historical-data', 'https://mojracun.incrementum.si/chartv2/public']

header={'User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2227.0 Safari/537.36'}

while True:

page1 = url[0]
page2 = url[1]
page3 = url[2]
page4 = url[3]

pagebtc = requests.get(page3, headers=header)

btc = BeautifulSoup(pagebtc.content, 'html.parser')

btcresult = btc.find(id='curr_table')

btccontent = btcresult.find_all('td')
refdate = btccontent[0].get_text() # Reference all asset dates to this
day = pd.to_datetime(refdate).day 
month = pd.to_datetime(refdate).month
year = pd.to_datetime(refdate).year
today = str(month) + '/' + str(day) + '/' + str(year) # Will be saved to google sheets



btcdate = []
btcprice = []
btcopen = []
btchigh = []
btclow = []
btcvol = []
btchange = []

k = 1
for each in btccontent:
    if k%7 == 1:
        btcdate.append(each.text.strip())
    if k%7 == 2:
        btcprice.append(each.text.strip())
    if k%7 == 3:
        btcopen.append(each.text.strip())
    if k%7 == 4:
        btchigh.append(each.text.strip())
    if k%7 == 5:
        btclow.append(each.text.strip())
    if k%7 == 6:
        btcvol.append(each.text.strip())
    if k%7 == 0:
        btchange.append(each.text.strip())
    k += 1
print(btcdate[0] + ' ' + btcprice[0] + ' ' + btchange[0])


btprice = btcprice[0]
btchange = btchange[0]

row = [today, btprice, btchange]
print(row)
    
# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('stock-0e7acf94311d.json', scope)
client = gspread.authorize(creds)
sheet = client.open("Stock Trading Chart")
sheet1 = sheet.worksheet("Sheet1")
sheet1.add_rows(index)
sheet1.insert_row(row, index, value_input_option='USER_ENTERED')
#sheet1.
index += 1
time.sleep(10)

如果您知道为什么会这样，请指导我。请注意，“while”循环下面的代码是缩进的

标签： pythonweb-scrapinggoogle-sheetsgspread

解决方案

推荐阅读