2
votes

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?

1
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

1 Answers

1
votes

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.