谷歌表格API v4如何获取空单元格

Question

我有一个Google表格，其中一列可能没有信息。 当迭代行并查看该列时，如果该列为空，它不返回任何内容。更糟糕的是，如果我获取完整行并包括该列，例如获取5列，则当任何列为空时，仅会返回4列。 如果在列中的一个单元格为空，如何在获取行列时返回NULL或空字符串？

// Build a new authorized API client service.
Sheets service = GoogleSheets.getSheetsService();
range = "Functional Users!A3:E3";
response = service.spreadsheets().values().get(spreadsheetId, range).execute();
values = response.getValues();
cells = values.get(0);

我在一行中获取了5个单元格。cells.size()应该始终返回五个。但是，如果这5个单元格中有任何一个为空，它将返回较少的单元格。比如只有B3单元格为空，cells.size()将为4。下一次迭代，我获取A4:E4并且D4单元格为空。同样，cells.size()将为4。没有办法知道哪个单元格缺失。如果A4、D4和E4都为空，则cells.size()将为2。

如何使其无论是否为空都返回5个单元格？

- GregMa

Answer 1

我曾经涉猎过Sheetsv4，当您读取一系列没有数据的单元格时，确实会出现这种行为。看起来这就是设计方式。如阅读数据文档中所述：

省略空尾随行和列。

因此，如果您能找到一种表示“空值”的字符，例如零，则可以采用这种方法之一。

- ReyAnthonyRenacia

---

我不明白为什么会这样实施，至少应该提供一个选项。为什么要让用户指定范围，然后根据用户可能无法控制的因素返回可能不同的范围？我想要一个范围，给我我要求的范围，不多也不少，就像每个API请求到任何其他API一样。 - Milton

@Milton 我认为Google想要在响应的有效载荷上节省一些空间。虽然这很愚蠢，但它可以在传输过程中节省一些字节。 - ariestav

Answer 2

我解决这个问题的方式是将值转换为Pandas数据框。我从Google Sheets中获取了我想要的特定列，然后将这些值转换为Pandas数据框。一旦我将数据集转换为Pandas数据框，我进行了一些数据格式化，然后将数据框转换回列表。通过将列表转换为Pandas数据框，每列都会被保留。Pandas已经为空尾行和列创建了空值。但是，我还需要将具有空值的非尾行转换以保持一致性。

# Authenticate and create the service for the Google Sheets API
credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SCOPES)
http = credentials.authorize(Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?version=v4')
service = discovery.build('sheets', 'v4',
    http=http,discoveryServiceUrl=discoveryUrl)

spreadsheetId = 'id of your sheet'
rangeName = 'range of your dataset'
result = service.spreadsheets().values().get(
    spreadsheetId=spreadsheetId, range=rangeName).execute()
values = result.get('values', [])

#convert values into dataframe
df = pd.DataFrame(values)

#replace all non trailing blank values created by Google Sheets API
#with null values
df_replace = df.replace([''], [None])

#convert back to list to insert into Redshift
processed_dataset = df_replace.values.tolist()

- Chase Wright

---

将数据转换为pandas dataframe是个好主意。在我的情况下，这尤其有用，因为源数据有合并的列，使得解析变得更加复杂。 - Gani Simsek

你可以使用fillna()将null转换为“”。这可能更有效，因为我会假设null值是稀疏的。哦，实际上运行效率可能是相同的。我不知道pandas如何实现fillna和replace。 - CrazyFrog

问答者最初是否提出了这个问题？因为他的问题中没有任何相关内容。问答者正在询问如何处理当存在空列时的情况。因此，空列甚至不会返回一个空字符串。values将是所有列的大小。 - NONONONONO

Answer 3

我在使用 Sheets API 的 V4 版本时也遇到了同样的问题，但是通过在我的范围末尾添加一个额外的列和在 values.get API 中使用 valueRenderOption 参数，我成功地解决了这个问题。

假设有三列 A、B 和 C，其中任何一列都可能包含空值，请添加另外一列 D，并在此处添加任意值，例如“blank”。

确保你在你的范围中捕获新列并添加额外的参数，

valueRenderOption: 'FORMATTED_VALUE'。

你应该得到类似于以下的调用：

sheets.spreadsheets.values.get({
  spreadsheetId: SOME_SHEET_ID,
  range: "AUTOMATION!A:D",
  valueRenderOption: 'FORMATTED_VALUE'
}, (err, res) => {})

这将为每个值提供一个一致长度的数组，在空单元格值的位置返回空字符串 ""。

- James

---

直到我最后一列有空单元格，我才明白额外列的用途...但现在我懂了;-)。现在指定 valueRenderOption=FORMATTED_VALUE 给我在列表中提供了一些 u''，这正是我需要的！你救了我的一天。谢谢。 - Christophe Muller

Answer 4

如果您从Google表格API v4中选择一个范围，那么如果空行数据位于所选范围的开头或中间，则会包括它。 只有在范围末尾没有数据的单元格才被省略。基于这个假设，您可以在应用程序代码中“填充”没有数据的单元格。

例如，如果您选择A1：A5并且A1没有值，则它仍将作为 {} 返回到行数据中。

如果A5没有，则您将得到长度为4的数组，因此知道要填写空的A5。 如果A4和A5为空，则您将得到长度为3的数组，依此类推。

如果范围中没有数据，则将收到空对象。

- Jafferwaffer

Answer 5

我知道已经很晚了，但以防将来有人遇到这个问题需要解决，我将分享我是如何克服这个问题的。 我所做的是将我要查找的单元格范围增加一个。然后在我正在读取的 Google 电子表格中，在额外的列（现在添加到数组中的列）中添加一行“.”。然后我保护了这行点，以防止它从“.”更改。 这种方法可以为您提供包括空结果在内的所有寻找内容的数组，但会增加数组大小1。但如果这让您感到不舒服，您可以制作一个新的，不带数组的最后一个索引。

- Roy Ernster

---

这应该是有效的答案。 - mcorbe

Answer 6

另一个选择是迭代返回的行，检查行的长度并附加您期望返回的任何数据。我发现这比向我的数据集添加无用数据更可取。

- Brad Bautista

Answer 7

我来晚了，但这里有另一个选择：

def read_sheet(service, SPREADSHEET_ID, range) -> pd.DataFrame:

    result = service.spreadsheets().values().get(spreadsheetId=SPREADSHEET_ID, range=range).execute()

    rows = result.get('values', [])

    df = pd.DataFrame(rows[0:])

    df.columns = df.iloc[0]

    df = df.drop(axis=0, index=0)

    return df

为了使这个解决方案起作用，您需要在要读取的电子表格的所有列中都有标题（列名）。它将加载一个没有标题（列名）规定的pandas df，用第一行替换列名，然后删除它。

- Mateus Souza

Answer 8

Sheets API V4应该返回所有空白列，直到最后一个填充的列。

这将填补空白：

values = result.get('values', [])
print(values[1:5]) # [['Spinach Lasagna', '10', '5', '', 'x'], ['Hot Dish', '10', '5', '', '', '', 'x'], ['Tuna-Noodle Casserole', '10', '5', '', 'x', '', '', 'x'], ['Sausage and Peppers', '10', '3', '', '', '', '', '', 'x']]

n_col = 14                                # hard code
n_col = max([len(i) for i in values])     # if last column is occupied at least once
n_col = len(values[0])                    # if you have header

values = [lst + ([''] * (n_col - len(lst))) for lst in values]

print(values[1:4]) # [['Spinach Lasagna', '10', '5', '', 'x', '', '', '', '', '', '', '', '', ''], ['Hot Dish', '10', '5', '', '', '', 'x', '', '', '', '', '', '', ''], ['Tuna-Noodle Casserole', '10', '5', '', 'x', '', '', 'x', '', '', '', '', '', '']]

- jpmorris

Answer 9

如果行中的最后一个单元格有值，则整行将被完全返回 例如：

行：

|Nick|29 years|Minsk|
|Mike|        |Pinsk|
|Boby|        |     |

返回：

[
  ["Nick", "29 years", "Minsk"],
  ["Mike", "", "Pinsk"]
  ["Boby"]
]

因此，当您添加一个新行时，如果单元格为空（""或null），请使用空格" "代替。

然后，当您读取值时，只需将所有空格" "的项目映射到空""即可。

行：

|Nick|29 years|Minsk|
|Mike|        |Pinsk|
|Boby|        |"  " |

返回：

[
  ["Nick", "29 years", "Minsk"],
  ["Mike", "", "Pinsk"]
  ["Boby", "", " "]
]

- NickUnuchek

Answer 10

我所能找到的唯一解决方案是编写你自己的函数：

def _safe_get(data, r, c):   
    try:
        return data[r][c]
    except IndexError:
        return ''

def read(range_name, service):
    result = service[0].spreadsheets().values().get(spreadsheetId=service[1],
                                                range=range_name).execute()
    return result.get('values', [])

def safe_read(sheet, row, col, to_row='', to_col='', service=None):
        range_name = '%s!%s%i:%s%s' % (sheet, col, row, to_col, to_row)
        data = read(range_name, service)

    if to_col == '':
        cols = max(len(line) for line in data)
    else:
        cols = ord(to_col.lower()) - ord(col.lower()) + 1
    if to_row == '':
        rows = len(data)
    else:
        rows = to_row - row + 1

    return [[_safe_get(data, r, c)
             for c in range(cols)]
            for r in range(rows)]

- Davoud Taghawi-Nejad