How to update google spreadsheet via sheets API v4

Question

I'm facing an issue within updating of existing spreadsheet on google drive, without using a batch update (right now I'm not sure whether it is even possible)

I already have existing spreadsheet with some data, then I retrieve file from google drive via

  Drive drive = new Drive.Builder(TRANSPORT, JSON_FACTORY, credential)
                .setApplicationName(APPLICATION_NAME)
                .build();
  drive.files().list().execute().getItems()

I easily match my file by the name and then I just want to update the spreadsheet in the founded file.

Sheets service = new Sheets.Builder(GoogleNetHttpTransport.newTrustedTransport(), JSON_FACTORY, credential)
                        .setApplicationName(APPLICATION_NAME)
                        .build();
Spreadsheet spreadsheet = service.spreadsheets().get(file.getId()).execute().setSheets(new ArrayList<>());

Here I get spreadsheet from file which match the name and then I just want to put there empy sheet (for example).

There is first point, where I'm not sure how to correctly "update" the spreadsheet on drive, because I cannot use

service.spreadsheets().create(spreadsheet).execute();

and I don't want to use batch update, because I already have set the spreadsheet content via setSheet() method.

I think it should be finalized with following:

Permission permission = drive.permissions().get(file.getId(), permissionId).execute().setType("anyone");
drive.permissions().update(
    spreadsheet.getSpreadsheetId(),
    permissionId,
    permission
).execute();

I was checking the google API here: https://developers.google.com/drive/v2/reference/permissions/update and here: https://developers.google.com/sheets/api/guides/batchupdate but they are using batch update.

What I am curious is why there is setSheets() method, which seems to be easily to use for updates, when I already created my sheets, but it seems that I just cannot simply update spreadsheet in existing file.

What is also interesting that every time I use the drive.perm.update, it creates a new file(with spreadsheet that I wanted to update) which actually doesn't make much sense to me.

So is there any chance to simply update file on drive and just use the spreadsheet method?

Any special reason you cant use batch update? I am assuming that you mean that you want to update / replace fully a specific sheet within on a google sheet document. — DaImTo
Yes you're right, I want to replace all sheets in sheet document. Well, when I saw the setSheets method it seems to me as a way more simple way than batch update and more readable for future use. Do you think that batch update is the only way? — Radim
Or can you please provide info how to create correct BatchUpdate request with list of sheets (List<Sheet> sheets). Because I only found out that I have to create Request and there is method setAddSheet with NewAddSheetReqeust. I've found that I can delete all sheets in spreadsheet but I didn't find method for insert collection of sheets. — Radim
@Priya well solutions suggested by me is not possible by now, so we decided to use the batch update operations described in the in link/answer related to this comment — Radim
@Radim....can u please provide information same on using that how to perform batch update from postman or rest client..what are the credentials required and where do available that credentials — Priya

Teyam Teyam · Accepted Answer · 2017-01-27T11:05:33

This statement by Jay Lee in this related SO post will give a clear insight:

The Google Drive API is meant for generic file / folder interactions. For directly modifying Google Spreadsheet data, the Google Sheets API would be the better choice.

Furthermore, to update Google spreadsheet, you may want to check Reading & Writing Cell Values which describes the basics of using the spreadsheets.values collection. It was mentioned that,

If you need to update formatting or other properties in a sheet, you will need to use the spreadsheets collection, which is described in Updating Spreadsheets.

For more information, please see Batch update operations and examples are shown here.

How to update google spreadsheet via sheets API v4

1 Answers