How to get around Google Sheets V4 API not returning empty cells

Question

I have a google sheet with several columns. We use this to keep login/password combinations for test accounts. I've been asked to automate the process of changing the password for each account every 90 days to meet IT requirements.

I've decided to use Java and the GoogleSheets V4 API. I need to open the sheet and gather all rows and iterate through them. For each row, get the login ID and password, generate a new password, change the password, then write back the row with the new password and the old password.

Right now, my stumbling block is that for cells that have no content in a row, it's not returning anything. Thus one row will have 5 columns and one row will have 4 columns. And there is no way I can find to know which column was empty and thus not returned.

Is there a solution to get it to return even empty cells?

    range = "'Functional Users'!A" + rowCount + ":E" + rowCount;
    response = service.spreadsheets().values().get(spreadsheetId, range).execute();
    values = response.getValues();
    cells = values.get(0);
    while (cells != null || cells.size() != 0)
    {
        //Iterate through the columns in the row (NOTE: If a column is blank, it will be missing here)
        //Cells(0) = Domain
        //Cells(1) = Ignored
        //Cells(2) = Account Name
        //Cells(3) = Email
        //Cells(4) = Password Status (Soon to be deprecated)
        if (cells.size() == 5)
        {

Answer 1

I had a similar use case and have used "CellFeeds" to tackle the issue.

I fetched the all the rows and columns from a Google Spreadsheet and had an issue that the empty / null values were getting missed and the number of columns was affected always.

You can try adding the "return-empty=true" as a query parameter to the URL of the CellFeed. It returns the empty values as null instead of circumventing those.

You can refer this page for detailed documentation on using Cell-based Feeds.

The following code might help you with your concern:

SpreadsheetService service =
    new SpreadsheetService("MyService");

URL SPREADSHEET_FEED_URL = new URL(
    "https://spreadsheets.google.com/feeds/cells/"+ "SpreadSheetID" +"/default/public/values");  //Replace SpreadSheetID with your spreadsheet ID, Note: Here the spreadsheet is shared publicly. If it is not published publicly, you need to change the URL and also workaround with the API keys.

// Fetch the first 10 rows of the spreadsheet
URL cellFeedUrl;
try {
    cellFeedUrl = new URI(SPREADSHEET_FEED_URL.toString()
        + "?return-empty=true&min-row=1&max-row=10").toURL(); //Here, return-empty=true is important
    CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);

    // Iterate through each cell, printing its value.
    for (CellEntry cell : cellFeed.getEntries()) {
        System.out.println(cell.getCell().getValue() + "\t");
    }
}
catch (URISyntaxException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

The result will give you the first 10 rows including the empty cells as null without affecting the column arrangement.

Hope this helps.