Google Sheets Java API Write to sheet

Question

I've been searching for an answer or an example to this a while. I started using google api sheets v4, and found the answer to append data to the spreadsheet, with the question in this stack question > Google Sheet API V4(Java) append Date in cells

/**
 * Application name.
 */
private static final String APPLICATION_NAME =
        "Google Sheets API Java Quickstart";

/**
 * Directory to store user credentials for this application.
 */
private static final java.io.File DATA_STORE_DIR = new java.io.File(
        System.getProperty("user.home"), ".credentials/sheets.googleapis.com-java-quickstart");

/**
 * Global instance of the {@link FileDataStoreFactory}.
 */
private static FileDataStoreFactory DATA_STORE_FACTORY;

/**
 * Global instance of the JSON factory.
 */
private static final JsonFactory JSON_FACTORY =
        JacksonFactory.getDefaultInstance();

/**
 * Global instance of the HTTP transport.
 */
private static HttpTransport HTTP_TRANSPORT;

/**
 * Global instance of the scopes, read/write commands depend on that
 * <p/>
 * If modifying these scopes, delete your previously saved credentials
 * at ~/.credentials/drive-java-quickstart
 */
private static final List<String> SCOPES =
        Arrays.asList(SheetsScopes.SPREADSHEETS);


static {
    try {
        HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        DATA_STORE_FACTORY = new FileDataStoreFactory(DATA_STORE_DIR);
    } catch (Throwable t) {
        t.printStackTrace();
        System.exit(1);
    }
}

/**
 * Creates an authorized Credential object.
 *
 * @return an authorized Credential object.
 * @throws IOException
 */
public static Credential authorize() throws IOException {
    // Load client secrets.
    InputStream in =
            SheetsWithAppendTest.class.getResourceAsStream("/client_secret.json");
    GoogleClientSecrets clientSecrets =
            GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));

    // Build flow and trigger user authorization request.
    GoogleAuthorizationCodeFlow flow =
            new GoogleAuthorizationCodeFlow.Builder(
                    HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
                    .setDataStoreFactory(DATA_STORE_FACTORY)
                    .setAccessType("offline")
                    .build();
    Credential credential = new AuthorizationCodeInstalledApp(
            flow, new LocalServerReceiver()).authorize("user");
    System.out.println(
            "Credentials saved to " + DATA_STORE_DIR.getAbsolutePath());
    return credential;
}

/**
 * Build and return an authorized Sheets API client service.
 *
 * @return an authorized Sheets API client service
 * @throws IOException
 */
public static Sheets getSheetsService() throws IOException {
    Credential credential = authorize();
    return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential)
            .setApplicationName("SHEETS_QUICK_START")
            .build();
}

public static String dateForReport() {
    DateFormat dateformat = new SimpleDateFormat("MM/dd/yy");
    Date dateObj = new Date();
    return dateformat.format(dateObj);
}

public static void main(String[] args) throws Exception {

    Sheets service = getSheetsService();

    String spreadSheetID = "xxx";
    Integer sheetID = 0;

    String defectReferenceValue = "Defect 9999";
    Double defectIDValue = 9999.0;
    String areaValue = "RandomArea";
    String screenValue = "";
    String fieldValue = "";
    String reqValue = "";
    String ruleValue = "";
    String fieldIDValue = "";
    String defectDescriptionValue = "";
    String screenShotValue = "ScreenShotLink";
    String dateValue = dateForReport();


    List<RowData> rowData = new ArrayList<RowData>();
    List<CellData> cellData = new ArrayList<CellData>();

    CellData defectReferenceField = new CellData();
    defectReferenceField.setUserEnteredValue(new ExtendedValue().setStringValue(defectReferenceValue));
    defectReferenceField.setUserEnteredFormat(new CellFormat().setNumberFormat(new NumberFormat().setType("DATE")));
    cellData.add(defectReferenceField);

    CellData defectIDField = new CellData();
    defectIDField.setUserEnteredValue(new ExtendedValue().setNumberValue(defectIDValue));
    cellData.add(defectIDField);

    CellData areaField = new CellData();
    areaField.setUserEnteredValue(new ExtendedValue().setStringValue(areaValue));
    cellData.add(areaField);

    CellData screenField = new CellData();
    screenField.setUserEnteredValue(new ExtendedValue().setStringValue(screenValue));
    cellData.add(screenField);

    CellData fieldField = new CellData();
    fieldField.setUserEnteredValue(new ExtendedValue().setStringValue(fieldValue));
    cellData.add(fieldField);

    CellData reqField = new CellData();
    reqField.setUserEnteredValue(new ExtendedValue().setStringValue(reqValue));
    cellData.add(reqField);

    CellData ruleField = new CellData();
    ruleField.setUserEnteredValue(new ExtendedValue().setStringValue(ruleValue));
    cellData.add(ruleField);

    CellData fieldIDField = new CellData();
    fieldIDField.setUserEnteredValue(new ExtendedValue().setStringValue(fieldIDValue));
    cellData.add(fieldIDField);

    CellData defectDescriptionField = new CellData();
    defectDescriptionField.setUserEnteredValue(new ExtendedValue().setStringValue(defectDescriptionValue));
    cellData.add(defectDescriptionField);

    CellData screenShotField = new CellData();
    screenShotField.setUserEnteredValue(new ExtendedValue().setStringValue(screenShotValue));
    cellData.add(screenShotField);

    CellData dateField = new CellData();
    dateField.setUserEnteredValue(new ExtendedValue().setStringValue(dateValue));
    cellData.add(dateField);

    rowData.add(new RowData().setValues(cellData));

    BatchUpdateSpreadsheetRequest batchRequests = new BatchUpdateSpreadsheetRequest();
    BatchUpdateSpreadsheetResponse response;
    List<Request> requests = new ArrayList<Request>();

    AppendCellsRequest appendCellReq = new AppendCellsRequest();
    appendCellReq.setSheetId(sheetID);
    appendCellReq.setRows(rowData);
    appendCellReq.setFields("userEnteredValue,userEnteredFormat.numberFormat");


    requests = new ArrayList<Request>();
    requests.add(new Request().setAppendCells(appendCellReq));
    batchRequests = new BatchUpdateSpreadsheetRequest();
    batchRequests.setRequests(requests);


    response = service.spreadsheets().batchUpdate(spreadSheetID, batchRequests).execute();
    System.out.println("Request \n\n");
    System.out.println(batchRequests.toPrettyString());
    System.out.println("\n\nResponse \n\n");
    System.out.println(response.toPrettyString());
}

Although the solution is functional, it's not scaleable, it adds values to the sheet incrementally, without any checks on header. What I really want to do is to add values like a hashmap, for the header row "Defect" = "x" header row "Date" = y etc.

Does anyone have a sample solution to that or can you direct me towards a resource I could use for the answer? Any help will be greatly appreciated.

Thank you in advance,

Nikos.

Answer 1

store a list of columns

for each item in the list..check for a value in your hashmap if the value is not present add a blank cell if the value is present put the value in the cell

Map<String,String> values=new HashMap<>();
.
.
.
String[] columns ={"greek legend","minataur"};
for(int i=0;i<columns.length;i++){
    String val=values.get(columns[i]);
    if(val!=null)
    {
        //put value in cell
    }
    else{
        //add blank cell
    }
}