24

How to get the last row with value in the new Google Sheets API v4 ?

i use this to get a range from a sheet:

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

how to detect the last row with value in the sheet ?

seba123neo
  • 4,688
  • 10
  • 35
  • 53

9 Answers9

38

You can set the range to "A2:D" and this would fetch as far as the last data row in your sheet.

A Paracha
  • 904
  • 8
  • 9
9

I managed to get it by counting the total rows from current Sheets. Then append the data to the next row.

rowcount = this.mService.spreadsheets().values().get(spreadsheetId, range).execute().getValues().size()
hellilyntax
  • 157
  • 4
  • 13
8

Rather than retrieving all the rows into memory just to get the values in the last row, you can use the append API to append an empty table to the end of the sheet, and then parse the range that comes back in the response. You can then use the index of the last row to request just the data you want.

This example is in 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.
  • 111
  • 1
  • 3
  • This is the best approach available since appending an empty table is actually a lightweight request. I've followed your advice and created a method to fetch the range with values. – Mauricio Apr 01 '21 at 15:08
  • It won't work if you have empty lines below the data. – Mauricio Mar 08 '22 at 21:18
  • Actually. Using dummy append for the range TabName!A:ZZZ workerd for me to retrieve the amount of lines. – Mauricio Mar 09 '22 at 11:48
3

Google Sheets API v4 does not have a response that help you to get the index of the last written row in a sheet (row that all cells below it are empty). Sadly, you'll have to workaround and fetch all sheet rows' into memory (I urge you to comment if I'm mistaken)

Example:

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)

Output:

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

enter image description here


If you wish to append more rows to the last row, see this

Jossef Harush Kadouri
  • 32,361
  • 10
  • 130
  • 129
1

You don't need to. Set a huge range (for example A2:D5000) to guarantee that all your rows will be located in it. I don't know if it has some further impact, may be increased memory consumption or something, but for now it's OK.

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;
    }

Look at the loop for (List row : values). If you have two rows in your table you will get two elements in values list.

yurart
  • 593
  • 1
  • 6
  • 23
  • 3
    You don't need to set up a large limit, by just specifying "A2:D" is enough to find the last value of A,B,C,D. – András Gyömrey Jun 29 '16 at 07:21
  • That's right! Thanks! I was not experienced enough back then :) – yurart Jun 29 '16 at 11:26
  • @AndrasGyomrey do you also know how to get only the cells that have a value -- without first retireving them and filtering them out? Like is there a built-in method for only retrieving them in a certian range> – Yaakov5777 Mar 17 '19 at 09:38
  • @Yaakov5777 I haven't been working lately with that. But I remember I had to do some normalization myself because the API *skipped* empty cells without asking. – András Gyömrey Mar 18 '19 at 11:11
0

Have a cell somewhere that doesn't interfere with your datarange with =COUNTA(A:A) formula and get that value. In your case

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

?

If there could be empty cells inbetween the formula would be a little more tricky but I believe it saves you some memories.

0

2022 Update

I I don’t know if this will be relevant for someone in 2022, but now you can do it differently. You can just set next value as range :

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

In resolve you get all data in column and range with last index. I tested it on js and php

basil.digital
  • 56
  • 1
  • 5
0

Following Mark B's answer, I created a function that performs a dummy append and then extracts the last row info from the dummy append's response.

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
  • 2,552
  • 2
  • 29
  • 43
0

A easy way to get the last raw into an array maybe this:

// 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();
Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
ejooroo
  • 39
  • 4