如何将获取的单元格数据的值读取为日期Google Sheets API [英] How to read value of fetched cell data as date google sheets API

问题描述

我有一个电子表格，其日期格式为 1月30日等，但具有实际的日期值。

我使用

该数字的格式是什么？我知道这不是unix时间戳b / c转换，它返回的日期不正确

更新2：解决方法

此代码有效，但使用python日期格式（它也假定年份为当前年份。但是显然并非总是如此）：

  def get_spreadsheet_sheets（service，电子表格ID）：
电子表格= service.spreadsheets（）。get（spreadsheetId = spreadsheetId）.execute（）
 dateCell = C15 $电子表格['sheets']中工作表的b $ b：
 sheetTitle = sheet ['properties'] ['title'] 
 rangeName =％s！％s％（sheetTitle，dateCell）
结果= service.spreadsheets（）。values（）。get（
电子表格ID = spreadsheetId，range = rangeName）.execute（）
 values = result.get（'values'，[]） 
 dateStr =％s％s％（值[0] [0]，datetime.date.today（）。year）
 cellDate = datetime.datetime.strptime（dateStr，'％b％ d％Y'）
打印（cellDate）
  

解决方案

怪异数字，称为序列化日期时间值或日期序列值。显示为日期的0值（yyyy-mm-dd）为 1899-12-30 。整数是天，分数是小时，分钟等。

相关：转换Google电子表格日期转换为JS Date对象？

引用：

• TO_DATE ，Google Spreadsheet内置函数


• DateTime序列号，Google表格中的文章部分> API v4指南


• Google Sheet API V4（Java）在单元格中附加日期，堆栈溢出Q& A

h / t 萨姆·柏林

I have a spreadsheet that have dates formatted as "Jan 30" etc, but have actual date values.

I fetch the data using python google Sheets API like so:

def get_spreadsheet_sheets(service, spreadsheetId):
    spreadsheet = service.spreadsheets().get(spreadsheetId=spreadsheetId).execute()
    dateCell = "C15"
    for sheet in spreadsheet['sheets']:
        sheetTitle = sheet['properties']['title']
        rangeName = "%s!%s" % (sheetTitle, dateCell)
        result = service.spreadsheets().values().get(
            spreadsheetId=spreadsheetId, range=rangeName).execute()
        values = result.get('values',[])

values gives me 'Jan 30', I would like to actually read it as 1/30/2017.. How can I do that?

update

Digger deeper into the spreadsheets.value.get http documentation (which corresponds to the python api client method references here I learned that I can put the option of valueRenderOption as UNFORMATTED_VALUE..

however when I run that I get this weird number for Jan 30: 42765

what format is that number exactly? I know it's not a unix time stamp b/c converting it returns a non-sensical date

update 2: workaround

this code works but using python date formatting (it also assumes that the year is the current year.. but obviously that won't always be the case):

def get_spreadsheet_sheets(service, spreadsheetId):
    spreadsheet = service.spreadsheets().get(spreadsheetId=spreadsheetId).execute()
    dateCell = "C15"
    for sheet in spreadsheet['sheets']:
        sheetTitle = sheet['properties']['title']
        rangeName = "%s!%s" % (sheetTitle, dateCell)
        result = service.spreadsheets().values().get(
            spreadsheetId=spreadsheetId, range=rangeName).execute()
        values = result.get('values',[])
        dateStr = "%s %s" % (values[0][0], datetime.date.today().year)
        cellDate = datetime.datetime.strptime(dateStr, '%b %d %Y')
        print(cellDate)

解决方案

The "weird number" it's called "serialized date-time value" or "date serial value". The 0 value displayed as date (yyyy-mm-dd) is 1899-12-30. Integers are days, fractions are hours, minutes, etc.

Related: Converting Google spreadsheet date into a JS Date object?

References:

• TO_DATE, Google Spreadsheet built-in function
• DateTime Serial Numbers, Article section from Google Sheets > API v4 Guide
• Google Sheet API V4(Java) append Date in cells , Stack Overflow Q&A

h/t Sam Berlin

这篇关于如何将获取的单元格数据的值读取为日期Google Sheets API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！