2

So I want to be able to create a sheet, populate it with some data, and create a column used for input, but I want to protect all other cells. I am using Java / eclipse to run the API.

The issue I am having seems to be a bug of some kind. When I open the document, it notifies me that I (the owner) am the only one who can edit the range, but when logged into an incognito window it states that "You and one other user can edit this sheet". Anyone with the link can edit any cell in the document, despite it stating that "Sheet1 except A1:A18" in the protected ranges.

Strange thing I noticed also, if I as owner go into Protected Ranges, click on my generated range and click Permissions -> Done, it finalizes my range to how it should behave. Problem is in the final product I cannot be going into these files manually.

The order I am going in is: Create File -> Add Data -> Set Unprotected range -> Set view able by anyone with link.

After adding the range, it looks on the sheet itself exactly how it would if I were to add it in myself, but it doesn't behave the same way. Has anyone had any luck with this process? I have included my request for the protected range below.

If you need any more information I will try and respond as soon as possible.

public Request unProtectRange(String id, GridRange range) {
    List<GridRange> grids = new ArrayList<GridRange>();
    grids.add(range);
    return new Request()
        .setAddProtectedRange(new AddProtectedRangeRequest()
            .setProtectedRange(new ProtectedRange()
                .setRequestingUserCanEdit(false)
                    .setRange(new GridRange())
                        .setUnprotectedRanges(grids)));
}
Andrew
  • 21
  • 1

1 Answers1

4

There's a few issues here. Per the ProtectedRange docs...

  • The requestingUserCanEdit field is read only, so setting that had no impact.
  • The editors field is not set, so the protected range is not actually being protected. It defaults to the same access controls as the spreadsheet itself. You must set editors to a valid Editors object.
  • The GridRange doesn't have a sheetId set on it. That means it will default to the sheet id 0. You probably want to explicitly set the sheet ID.
Sam Berlin
  • 3,603
  • 12
  • 23
  • and how to execute the request? like, i can execute `sheetService.spreadsheets().batchUpdate(sheetId, batchUpdateSpreadsheetRequest).execute();` but that works only for batchUpdateSpreadsheetRequest not for AddProtectedRangeRequest or Request – ses Jan 06 '22 at 18:04