2

Is there any way to find the last row you have written in a google spreadsheet in Java?

I tried to do that by having a variable which I keep in another file and I update that every time I do another writing. Is there any other way?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Katelyn
  • 121
  • 2
  • 3
  • 12

1 Answers1

4

Finding last written row in Google Spreadsheet API

I'll give you a concept first using REST calls but you'll have to apply that in Java.

  1. Exceed the range of cells where you're writing. So to find last written row between cells A1:A10, use the range A1:A11 or A1:B (using B means all the rows in cell A will be traversed).
  2. Fetch a range of cells, using GET. Parse the response result. And get the length of the parsed values. The length will always indicate the last row since Sheetsv4 ignores blank/empty cells.

So example I have a range of cells between A1:A10. I wrote "hello" in A10. Following the concepts above I do this:

..
xhr.open('GET', 'https://sheets.googleapis.com/v4/spreadsheets/'+myspreadsheetId+'/values/Sheet1!A1:A10);
..
xhr.onload = function (oEvent) {
arrayBuffer = xhr.response; 
myArray = JSON.parse(arrayBuffer);
console.log("last row is " + myArray.values.length)
.. //output is 'last row is 10'

This part of my XHR request returns 10. Now I know that the last written row in my sheet is A10.

To do this in Java use the solution based on this SO thread.

mService.spreadsheets().values().get("ID_SHEET", "Sheet1!A1:B").execute();

Also check this thread about writing on data cells using Java. I think it offers additional insight as well.

Community
  • 1
  • 1
ReyAnthonyRenacia
  • 17,219
  • 5
  • 37
  • 56
  • Its an old thread, I know, but it should actually be "Sheet1!A:A" if you want the values in the first column A, the range you use will also return the values in column B and that's not what you need, to count the rows. – Nicolas Zimmer Apr 23 '19 at 10:03
  • The question was to count rows, so returning only a single column has the danger that if the last row has data but the single column you return is empty in that last row your row count will be one less than the correct answer. – Mike Hanafey Mar 12 '22 at 15:28