-1

I'm trying to run the method Sheet.getData(), and am getting a NPE. From what I can get from the Javadocs, I needed to have requested a data range in order to not get a null value, but I cannot get any information on how to request a data range.

Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
        .setApplicationName(APPLICATION_NAME)
        .build();
ValueRange response = service.spreadsheets().values().get(spreadsheetId, range).execute();

Spreadsheet spreadsheet = service.spreadsheets().get(spreadsheetId).execute();
List<Sheet> sheets = spreadsheet.getSheets();
Sheet sheet = null;
for(Sheet s : sheets)
    if(s.getProperties().getTitle().equalsIgnoreCase("GSheets API Sheet"))
        sheet = s;

List<GridData> data = sheet.getData();
//System.out.println(data.size());

I'm doing it this way because I need to get a cell's background color, so if anyone can help me do this in a different way, I'd be open to other methods.

Jonathan M.
  • 1
  • 2

2 Answers2

0

I guess the exception is thrown at the commented line, when it isn't commented.

It means the data object is null, which means sheet.getData() returns null. So I guess something must actually be wrong with the infos you use to create your spreadsheet. The options are multiple. You should check the way you obtain the credentials and also the value of spreadsheetId.

Alexis Dufrenoy
  • 11,784
  • 12
  • 82
  • 124
  • So I need to do setRowGroups() before I get the data? – Jonathan M. Jul 30 '20 at 13:41
  • No, you need to make sure `sheet` is not null when you call a method on it. Calling a method on a null object is forbidden. So you can test nullness before you call a method on an object: `if (t!=null) {t.doSomething();}` or make sure beforehand the object is not null, which you didn't in your example. At the end of the for loop, you can' be sure sheet is not null. – Alexis Dufrenoy Jul 30 '20 at 13:43
  • I'm not trying to prevent a method from running if it is null, I'm trying to prevent it from being null in the first place. – Jonathan M. Jul 30 '20 at 13:46
  • I still don' know on which line you get your exception, but assuming it is on `sheet.setRowGroups()`, it means your loop is incorrect, because at the end of the loop, it seems that sheet is still null. – Alexis Dufrenoy Jul 30 '20 at 13:48
  • the `sheet.setRowGroups()` was a mistake that I forgot to remove. Sorry! The source code above is what my code actually is. Including the line on which the error is occurring (in hindsight, I probably should have included it initially). – Jonathan M. Jul 30 '20 at 14:11
  • I edited my answer. You could try to execute your code step b step and try to look what happens precisely. – Alexis Dufrenoy Jul 30 '20 at 20:49
0

s.getProperties().getTitle().equalsIgnoreCase("GSheets API Sheet") is never evaluating to true, so sheet is never set to a non-null value.

In other words, your workbook has no sheet named "GSheets API Sheet".

Always check to make sure your objects are not null before trying to call methods on them!!

Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
  • I already ruled that out. I ran multiple tests to see if sheets was null after the for loop, and it was not, I did have a sheet named that. From what I can tell, `Sheet.getData()` can return a null value if I do not assign a range, which I have no idea how to do without explicitly getting values. – Jonathan M. Jul 30 '20 at 21:13
  • I am well aware what a NullPointerException is, but I am wondering if anyone can tell me how to prevent `Sheet.getData()` from returning a null value in the first place, because `sheet` is not null. – Jonathan M. Jul 30 '20 at 21:15