Google Spread sheets, appending data using /java

Question

I've taken the code below from Write data to Google Sheet using Google Sheet API V4 - Java Sample Code @"Ashwin Karangutkar". Which writes to cells in the googlespread sheets.

But I really want to do, is to get this data appended to the sheets instead or writing on the first row. I've come across AppendCellsRequest function of the api, but I'm not too sure how to integrate it. Can anyone help me with this?

Thank you in advance.

Kind regards,

Nikos.

Aswin's code example

 public static void setValue(String SheetName,String RowStart, String RowEnd) throws IOException{
    // Build a new authorized API client service.
    Sheets service = getSheetsService();
    // Prints the names and majors of students in a sample spreadsheet:
    String spreadsheetId = "17PQyEPayrHSIgvPtrfZtItuUcArhvjDy-G68Xg02s3s";
    String range = SheetName+"!"+RowStart+":"+RowEnd;
    Integer sheetLocationNumber = 0;


    List<List<Object>> arrData = getData();

    ValueRange oRange = new ValueRange();
    oRange.setRange(range); // I NEED THE NUMBER OF THE LAST ROW
    oRange.setValues(arrData);

    List<ValueRange> oList = new ArrayList<>();
    oList.add(oRange);

    BatchUpdateValuesRequest oRequest = new BatchUpdateValuesRequest();
    oRequest.setValueInputOption("RAW");
    oRequest.setData(oList);

    BatchUpdateValuesResponse oResp1 = service.spreadsheets().values().batchUpdate(spreadsheetId, oRequest).execute();

    // service.spreadsheets().values().update (spreadsheetId, range,) ;
    //return request;
}

public static List<List<Object>> getData ()  {

// Add data to horizontal axis (same row)

    List<Object> data1 = new ArrayList<Object>();
    data1.add ("Example");
    data1.add ("Example2");
    System.out.println("Values");

// add data to vertical axis (new row)

    List<List<Object>> data = new ArrayList<List<Object>>();
    data.add (data1);

    return data;
}

score 2 · Answer 1 · answered Sep 16 '16 at 12:25

2

If you want to append data (and don't care about formatting or other properties of a cell), the easiest thing is to use spreadsheets.values.append. The guide for example usage is here.

Usage is similar to values.update or values.get.

answered Sep 16 '16 at 12:25

Sam Berlin

3,603
12
23

score 1 · Answer 2 · edited May 23 '17 at 12:17

I was able to append data to my own Google spreadsheet using java, I took the answer from this stackflow question Google Sheet API V4(Java) append Date in cells and altered it to add my data, I've the example of the code below.

I admit, it's very poorly done, it's not scale-able atm, I'm going to make a hashMap/list to match the values with the headers so I can update the fields correctly, instead of updating it them blindfold as I'm atm.

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

        Sheets service = getSheetsService();

        String spreadSheetID = "xx";
        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());
    }

I hope this helps for other people looking for similar answers.

Regards,

Nikos.

where did you pass sheet name or you mentioned it by numeric 0 ? — Ankit, Mar 15 '17 at 06:42

score -1 · Answer 3 · edited Jul 19 '22 at 08:47

-1

String content ="just now";
List<Object> data1 = new ArrayList<>();
data1.add (content);
List<List<Object>> data = new ArrayList<>();
data.add (data1);
ValueRange valueRange=new ValueRange();
valueRange.setValues(data);
service.spreadsheets().values().
append(spreadsheetId, range, valueRange)
        .setValueInputOption("RAW")
        .execute();

the above code worked fine

edited Jul 19 '22 at 08:47

Adriaan

17,741
7
42
75

answered Mar 10 '17 at 18:29

Raghav

1
2

Please read [answer] and [edit] your question to contain an explanation as to why this code would actually solve the problem at hand. Always remember that you're not only solving the problem, but are also educating the OP and any future readers of this post. – Adriaan Jul 19 '22 at 08:47
You don't explain the variable `range` – CeePlusPlus Feb 24 '23 at 01:55

Google Spread sheets, appending data using /java

3 Answers3