谷歌表格API v4 - 如何获取最后一行的值？

Question

如何在新版Google Sheets API v4中获取具有值的最后一行？

我使用以下代码从表格中获取一个范围：

mService.spreadsheets().values().get("ID_SHEET", "Sheet1!A2:B50").execute();

如何检测表格中的最后一行是否有值？

- seba123neo

Answer 1

您可以将范围设置为"A2:D"，这将获取您工作表中最后一行的所有数据。

- A Paracha

---

是的，但这种方式可能会获取大量数据，内存分配可能是一个问题。 - Pievis

Answer 2

我通过计算当前表格的总行数来获取它。 然后将数据追加到下一行。

rowcount = this.mService.spreadsheets().values().get(spreadsheetId, range).execute().getValues().size()

- hellilyntax

---

如果你想在末尾添加更多行，请查看这个链接。 - Jossef Harush Kadouri

Answer 3

不必将所有行都检索到内存中以获取最后一行的值，可以使用append API将一个空表格附加到工作表的末尾，然后解析响应中返回的范围。然后您可以使用最后一行的索引仅请求所需数据。

以下示例是使用Python实现的：

#empty table
table = {
    'majorDimension': 'ROWS',
    'values': []
}

# append the empty table
request = service.spreadsheets().values().append(
    spreadsheetId=SPREADSHEET_ID,
    range=RANGE_NAME,
    valueInputOption='USER_ENTERED',
    insertDataOption='INSERT_ROWS',
    body=table)

result = request.execute()

# get last row index
p = re.compile('^.*![A-Z]+\d+:[A-Z]+(\d+)$')
match = p.match(result['tableRange'])
lastrow = match.group(1)

# lookup the data on the last row
result = service.spreadsheets().values().get(
    spreadsheetId=SPREADSHEET_ID,
    range=f'Sheetname!A{lastrow}:ZZ{lastrow}'
).execute()

print(result)

- Mark B.

---

这是目前可用的最佳方法，因为向空表追加实际上是一种轻量级请求。我遵循了您的建议并创建了一个获取带有值范围的方法。 - Mauricio

如果数据下面有空行，它将无法正常工作。 - Mauricio

实际上，对于范围TabName!A:ZZZ，使用虚拟附加(dummy append)对我来说可以检索出行数。 - Mauricio

Answer 4

Google Sheets API v4没有提供一个帮助你获取工作表中最后一行索引（该行下所有单元格为空）的响应。不幸的是，你需要采用变通方法并将所有工作表行读入内存（如果我有误，请务必评论纠正）

示例：

spreadsheet_id = '1TfWKWaWypbq7wc4gbe2eavRBjzuOcpAD028CH4esgKw'
range = 'Sheet1!A:Z'

rows = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range).execute().get('values', [])
last_row = rows[-1] if rows else None
last_row_id = len(rows)
print(last_row_id, last_row)

输出：

13 ['this', 'is ', 'my', 'last', 'row']

---

如果您希望将更多行添加到最后一行，请查看此内容。

- Jossef Harush Kadouri

Answer 5

根据Mark B的回答，我创建了一个函数，执行虚拟附加操作，然后从虚拟附加响应中提取最后一行信息。

def get_last_row_with_data(service, value_input_option="USER_ENTERED"):
    last_row_with_data = '1'
    try:

        dummy_request_append = service.spreadsheets().values().append(
            spreadsheetId='<spreadsheet id>',
            range="{0}!A:{1}".format('Tab Name', 'ZZZ'),
            valueInputOption='USER_ENTERED',
            includeValuesInResponse=True,
            responseValueRenderOption='UNFORMATTED_VALUE',
            body={
                "values": [['']]
            }
        ).execute()

        a1_range = dummy_request_append.get('updates', {}).get('updatedRange', 'dummy_tab!a1')
        bottom_right_range = a1_range.split('!')[1]
        number_chars = [i for i in list(bottom_right_range) if i.isdigit()]
        last_row_with_data = ''.join(number_chars)

    except Exception as e:
        last_row_with_data = '1'

    return last_row_with_data

- Mauricio

Answer 6

你不需要这么做。设置一个巨大的范围（例如A2:D5000）可以确保所有行都在其中。我不知道它是否会产生进一步影响，可能会增加内存消耗或其他什么，但目前还可以。

private List<String> getDataFromApi() throws IOException {
        String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"; 
        String range = "A2:D5000";
        List<String> results = new ArrayList<String>();
        ValueRange response = this.mService.spreadsheets().values()
                .get(spreadsheetId, range)
                .execute();
        List<List<Object>> values = response.getValues();
        if (values != null) {
            results.add("Name, Major");
            for (List row : values) {
                results.add(row.get(0) + ", " + row.get(3));
            }
        }
        return results;
    }

看一下循环for (List row : values)。如果你的表格中有两行，那么values列表中将会有两个元素。

- yurart

---

不需要设置一个较大的限制，只需指定"A2:D"即可找到A、B、C、D列中的最后一个数值。 - Andras Gyomrey

没错！谢谢！当时我还不够有经验 :) - yurart

@AndrasGyomrey，您是否知道如何仅获取具有值的单元格 - 而无需先检索它们并将其过滤掉？例如，是否有一种内置方法仅在特定范围内检索它们？ - Yaakov5777

@Yaakov5777 我最近没有在那方面工作。但我记得我不得不自己进行一些规范化，因为API在没有询问的情况下跳过了空单元格。 - Andras Gyomrey

Answer 7

2022 更新

我不知道这个对于 2022 年的某个人是否有用，但现在你可以以不同的方式做到这一点。 你只需要将下一个值设置为范围：

const column = "A"
const startIndex = 2
const range = column + startIndex + ":" + column

在resolve中，您可以获取列和范围内的所有数据以及最后一个索引。我已在js和php上进行了测试。

- basil.digital

Answer 8

在某个单元格中使用 =COUNTA(A:A) 公式，以获取不干扰您数据范围的值。 在您的情况下

=MAX(COUNTA(A:A50),COUNTA(B:B50))

?

如果公式中间可能有空单元格，那么它会变得更加棘手，但我相信这可以节省一些内存。

- Kantumrobot

Answer 9

一个简单的方法将最后一行放入数组中可能是这样的：

// Google sheet npm package
const { GoogleSpreadsheet } = require('google-spreadsheet');

// File handling package
const fs = require('fs');

// spreadsheet key is the long id in the sheets URL
const RESPONSES_SHEET_ID = 'XXXXXXXXXXXXXXX';


// Create a new document
const doc = new GoogleSpreadsheet(RESPONSES_SHEET_ID);


// Credentials for the service account
const CREDENTIALS = JSON.parse(fs.readFileSync('CREDENTIAL_FILE.json'));

const getLastRow = async () => {

    // use service account creds
    await doc.useServiceAccountAuth({
        client_email: CREDENTIALS.client_email,
        private_key: CREDENTIALS.private_key
    });

    // load the documents info
    await doc.loadInfo();

    // Index of the sheet
    let sheet = doc.sheetsByIndex[0];

    // Get all the rows
    let rows = await sheet.getRows();

    console.log(rows[rows.length-1]._rawData);

};

getLastRow();

- ejooroo