Create new sheet in Google sheet document

Question

I want to create new sheet in google sheet document using Java API v4. I tested this code:

        Sheets service = getSheetsService();

        Spreadsheet sheet = new Spreadsheet();
        SpreadsheetProperties properties = new SpreadsheetProperties();
        properties.setTitle("new SpreadSheetTitle");
        sheet.setProperties(properties);

        Spreadsheet response = service.spreadsheets().create(sheet)
            .execute();

But when I open the document I don't see new page. What is the proper way to implement this?

P.S

I managed to create this:

List<List<Object>> writeData = new ArrayList<>();

        List<Object> dataRow = new ArrayList<>();
        dataRow.add("data 1 " + timestamp);
        dataRow.add("data 2 " + timestamp);
        dataRow.add("data 3 " + timestamp);
        dataRow.add("data 4 " + timestamp);
        dataRow.add("data 5 " + timestamp);
        writeData.add(dataRow);

        requests.add(new Request().setAddSheet(new AddSheetRequest()
            .setProperties(new SheetProperties().setTitle("scstc"))));


        BatchUpdateSpreadsheetRequest body
            = new BatchUpdateSpreadsheetRequest().setRequests(requests);
        BatchUpdateSpreadsheetResponse response = service.spreadsheets().batchUpdate(spreadsheetId, body).execute();

I can create empty sheet but how I can inset test data?

As can be seen in API, a `SpreadSheet` contains a list of `Sheet`. When you open a document, by default you are always looking at the first `Sheet` of said document. If your document does not have a `Sheet`, there is nothing to see. I don't see you actually adding a new `Sheet` to `sheet`, which could be why you see an empty document. See https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets — CthenB, Dec 27 '16 at 10:10
@Peter_Penzov https://developers.google.com/sheets/api/guides/batchupdate#example Also, creating the actual document first is probably going to be your next step. See this answer for more help in that regard: http://stackoverflow.com/questions/19361814/create-spreadsheet-using-google-spreadsheet-api-in-google-drive-java#19365988 — CthenB, Dec 27 '16 at 10:34
The first link (of my second comment) I gave you redirects to the official V4 API documentation, so I'd certainly hope so! — CthenB, Dec 27 '16 at 10:50
I managed to create empty sheet but how I can add some test data? — Peter Penzov, Dec 27 '16 at 12:15
Peter, this is literally the next page in the documentation examples. https://developers.google.com/sheets/api/samples/writing I'm marking this to be closed as you're not asking specific questions / even providing others with the solution. — CthenB, Dec 27 '16 at 12:59
I managed to do it but with separate request. How I can do it with the same request? — Peter Penzov, Dec 27 '16 at 13:04

score 2 · Answer 1 · answered May 08 '21 at 13:18

You created a new tab in your document If you want to add some columns when you create it, you can do it like this:

ValueRange appendBody = new ValueRange()
                .setValues(Collections.singletonList(
                        Arrays.asList("ColumnName1", "ColumnName2")));

getSheetsService().spreadsheets().values()
                .append(SPREADSHEET_ID, "TITLE_OF_YOUR_NEW_TAB", appendBody)
                .setValueInputOption("USER_ENTERED")
                .setInsertDataOption("INSERT_ROWS")
                .setIncludeValuesInResponse(true)
                .execute();

This code just inserts data to new tab with name "TITLE_OF_YOUR_NEW_TAB"

public Sheets getSheetsService() throws IOException, GeneralSecurityException {
        Credential credential = authorize();
        return new Sheets.Builder(GoogleNetHttpTransport.newTrustedTransport(), JSON_FACTORY, credential)
                .build();
    }

it does not add new tab (sheet). it add values to existing – ses Jan 04 '22 at 21:53 — ses, Jan 04 '22 at 21:53

score 2 · Answer 2 · answered Jan 13 '23 at 19:52

    Sheets sheetsService = SheetsServiceUtil.getSheetsService();

    List<Request> requests = new ArrayList<>();
    requests.add(new Request().setAddSheet(new AddSheetRequest().setProperties(new SheetProperties()
            .setTitle("NEW_TAB"))));
    BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests);

    sheetsService.spreadsheets().batchUpdate(SPREADSHEET_ID, body)
            .execute();

score 0 · Answer 3 · answered Jan 07 '22 at 16:18

If you want to update or append data to an existing Sheet (Tab) inside your spreadsheet you can use the following for update:

sheetsService.spreadsheets()
                    .values()
                    .update(spreadsheetId, "NAME_OF_EXISTING_SPREADSHEET", body)
                    .execute();

Or this one for append:

sheetsService.spreadsheets()
                    .values()
                    .append(spreadsheetId, "NAME_OF_EXISTING_SPREADSHEET", body)
                    .execute();

This works only if the sheet(tab) is already created.

Create new sheet in Google sheet document

3 Answers3