Protect Sheet and Range

[Linux_01]

Hello Team, 

Do we have any gam command which will work on "Protect Sheet and Range" option of Google Sheet to work upon?

I want to provide range of columns to be protect to edit and allow only 1st column to edit for a specific user for a GSheet. And this for multiple different users.

Thank you.

Regards,

Linux_01   

[Brian Kim]

No, not possible with GAM.

Apps Script would be better for this use case.

https://developers.google.com/apps-script/reference/spreadsheet/protection

[Ross Scroggs]

Advanced GAM can update sheets, see: https://github.com/taers232c/GAMADV-XTD3/wiki/Users-Spreadsheets#update-spreadsheets

You'll have to provide the JSON data required by the API: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#ProtectedRange

Ross

--

ross.s...@gmail.com

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/37d920f8-3529-483c-a745-d77e7013acc1n%40googlegroups.com.

[Ross Scroggs]

Here is a sample JSON file, Sheet.json

N is the sheet ID.

Column X is the column that us...@domain.com can edit (columns are 0 origin)

Columns X+1 to Y-1 are not editable (the closing index is open)

gam us...@domain.com update sheet <DriveFileItem> json file Sheet.json

{"requests":

  [

    {"addProtectedRangeRequest":

      {"protectedRange":

        {"range": {"sheetId": N, "startColumnIndex": X, "endColumnIndex": X+1},

         "editors": {"users": ["us...@domain.com"]}

        }

      }

    },

    {"addProtectedRangeRequest":

      {"protectedRange":

        {"range": {"sheetId": N, "startColumnIndex": X+1, "endColumnIndex": Y}

        }

      }

    }

  ]

}

--

Ross Scroggs

ross.s...@gmail.com

[Brian Kim]

Wow! I stand corrected:)

You received this message because you are subscribed to a topic in the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-manager/6UE47mgfNus/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAJkvRS_2tQ5O3iOSjMxoatzLF_sNzUdJ%3DYaeNfmye1vYmOMa0A%40mail.gmail.com.

[Gabriel Clifton]

If everyone knew everything, we would not need this group.

"And knowing is half the battle" --G.I. Joe

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CA%2B0bb36%2B8CGpZW4v_VBXV4UgeD3N81Da%3Dtc0SJKw6Tgx51FqFQ%40mail.gmail.com.

--

Gabriel Clifton | Network Administrator

Fort Stockton ISD | Technology Center
gabriel...@fsisd.net | http://www.fsisd.net
Office (432) 336-4055 ext 2

Fax (432) 336-4050
1204 W. Second St., Fort Stockton, TX 79735

CONFIDENTIALITY NOTICE: The contents of this email message and any attachments are intended solely for the addressee(s) and may contain confidential and/or privileged information and may be legally protected from disclosure. If you are not the intended recipient of this message or their agent, or if this message has been addressed to you in error, please immediately alert the sender by reply email and then delete this message and any attachments. If you are not the intended recipient, you are hereby notified that any use, dissemination, copying, or storage of this message or its attachments is strictly prohibited.

"You must always be willing to work without applause."
— Ernest Hemingway

"You just have to find that thing that's special about you that distinguishes you from all the others, and through true talent, hard work, and passion, anything can happen."
— Dr. Dre

[Kim Nilsson]

Yeah, if Apps Script can do it via API, much of it should be possible through gam, but you have to push the right information.

Perhaps if the Sheet is supposed to do some magic with the information in it, it might be easier to do with Apps Script.

Maybe mostly because StackOverflow is full of people who have already asked and answered it. :-)

[Linux_01]

Hi Ross,

I want to allow modification on Column "A" only and Rest should be disabled for modification.

I am not able to find the Sheet ID though. I have send the query separately to the group. 

I have modified the json file:

{"requests":

  [

    {"addProtectedRangeRequest":

      {"protectedRange":

        {"range": {"sheetId": N, "startColumnIndex": A, "endColumnIndex": A+1},

         "editors": {"users": ["us...@domain.com"]}

        }

      }

    },

    {"addProtectedRangeRequest":

      {"protectedRange":

        {"range": {"sheetId": N, "startColumnIndex": A+1, "endColumnIndex": J}

        }

      }

    }

Is this right?

[Kim Nilsson]

The SheetID is the unique part of the web address of the Sheet.

Just copy it from the address bar of your browser.

/Kim

--

There is No Substitute!

[Ross Scroggs]

You can get the Sheet ID like this or from the end of the URL ask Kim suggested: &gid=N

gam user us...@domain.com info sheet <DriveFileItem>

Columns are referred to by their indexes, not names and the closing index is 1 greater than the desired column.

{"requests":

  [

    {"addProtectedRangeRequest":

      {"protectedRange":

        {"range": {"sheetId": N, "startColumnIndex": 0, "endColumnIndex": 1},

         "editors": {"users": ["us...@domain.com"]}

        }

      }

    },

    {"addProtectedRangeRequest":

      {"protectedRange":

        {"range": {"sheetId": N, "startColumnIndex": 1, "endColumnIndex": 10}

        }

      }

    }

Ross

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/0c8a7cde-b312-4685-b017-75c2b0074c3an%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Linux_01]

Hi Ross,

Sheet ID from URL wont help me as I automating.

However I tried the command suggested by you but no luck:

gam user test...@domain.com info sheet testuser-1617002393

User: test...@domain.com, Show Info 1 Spreadsheet

  User: test...@domain.com, Spreadsheet: testuser-1617002393, Show Info Failed: Requested entity was not found.

Please help 

[Linux_01]

This GSheet is from shared drive.

[Ross Scroggs]

This is b=going to go faster if you contact me directly; send me a Meet/Zoom invitation.

Ross

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/4ca37ac2-a76e-48a5-9e55-6ee50bd8dc1fn%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Ross Scroggs]

In this command: gam user test...@domain.com info sheet testuser-1617002393

testuser-1617002393 is not a Drive File ID, hence the command didn't work.

I'm unclear on your requirement: are there multiple Google Sheets you're trying to protect?

Are there all on the same Team Drive or Multiple Team Drives?

Please explain in detail what your requirements are.

Thanks

Ross

--

Ross Scroggs

ross.s...@gmail.com

[Linux_01]

Hi Ross,

Yes, I have many such google sheets where I want to allow respective user to edit only 1st column of the Google sheet and lock rest of the columns.

These Google Sheets are present in the Single Shared Drive. Each Google Sheet has only one Sheet/tab.

Each domain user has its respective Google Sheet to be protected.

and I am unable to find these Google Sheet ID to move ahead as guided by you to use Json File to Protect the sheet.

Hope this helps.

Thank you.

[Kim Nilsson]

Then you need to start by getting a list of your files. 

https://github.com/taers232c/GAMADV-XTD3/wiki/Users-Drive-Files-Display#file-selection-starting-point-for-display-file-list 

[Linux_01]

Hi Ross,

None of the below mentioned commands list the files shared from a Shared Drive:

All files shared with me regardless of ownership: gam user testuser show filelist fullquery "sharedWithMe=True"

Files owned by others shared with me: gam user testuser show filelist fullquery "sharedWithMe=True and not 'me' in owners"

Files owned by me shared with myself: gam user testuser show filelist fullquery "sharedWithMe=True and 'me' in owners"

I am sure there must be a different way to list the files shared through Shared Drives.

[Kim Nilsson]

You need to use the select option, like the wiki tells you.

/Kim 

[Linux_01]

For Testing, I manually found the Sheet ID to move ahead and test with json file.

I am getting error message as mentioned below:

gam user test...@domain.com update sheet "testuser-1617693318" json Sheet.json 

Command: /home/admin/bin/gamadv-xtd3/gam user test...@domain.com update sheet testuser-1617693318 json Sheet.json >>><<<

ERROR: Expecting value: line 1 column 1 (char 0): Sheet.json

Help: Syntax in file /home/jmadur/bin/gamadv-xtd3/GamCommands.txt

Help: Documentation is at https://github.com/taers232c/GAMADV-XTD3/wiki

Please suggest.

[Brian Kim]

The syntax should include file

gam user test...@domain.com update sheet "testuser-1617693318" json file Sheet.json 

https://github.com/taers232c/GAMADV-XTD3/wiki/Users-Spreadsheets#update-spreadsheets

[Jyoti Madur]

Still the same:

gam user test...@domain.com update sheet "testuser-1617693318" json file Sheet.json

Command: /home/admin/bin/gamadv-xtd3/gam user test...@domain.com update sheet testuser-1617693318 json file Sheet.json >>><<<

ERROR: Expecting ',' delimiter: line 9 column 32 (char 110): Sheet.json
Help: Syntax in file /home/admin/bin/gamadv-xtd3/GamCommands.txt

Help: Documentation is at https://github.com/taers232c/GAMADV-XTD3/wiki

--

You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/ba03c013-f20c-4a81-aa02-b0a032108952n%40googlegroups.com.

--

Regards,

Jyoti Madur

[Ross Scroggs]

Jyoti,

Here is a corrected Sheet.json that assumes a sheetID of 0.

{"requests":

  [

    {"addProtectedRange":

      {"protectedRange":

        {"range": {"sheetId": 0, "startColumnIndex": 0, "endColumnIndex": 1},

         "editors": {"users": ["us...@domain.com"]}

        }

      }

    },

    {"addProtectedRange":

      {"protectedRange":

        {"range": {"sheetId": 0, "startColumnIndex": 1}

        }

      }

    }

  ]

}

Again, if you want additional help, contact me directly. We're never going to get what you want via email.

I'm in California (PDT, what time zone are yiu in?

Ross

ross.s...@gmail.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAMUDA4_U7dW0nB01e%3DUsHw50AOdrzks2%3D1Kx3vHvy_s5WOanqw%40mail.gmail.com.

--

Ross Scroggs

ross.s...@gmail.com

[Linux_01]

Hi Ross,

I tried with the updated Sheet.json file and getting the new error message as mentioned below:

gam user test...@domain.com update sheet "<FileID>" json file Sheet.json
User: test...@domian.com, Update 1 Spreadsheet
User: test...@domain.com, Spreadsheet: <FileID>, Update Failed: Invalid requests[0].addProtectedRange: No grid with id: 0

We are different Time zone although I have sent an Email to your gmail ID.

Thank you.

[Ross Scroggs]

You have to put in the correct sheetId, the sample shows 0 which is not correct.

Again, I'm in California (PDT, what time zone are you in?

Ross

--

ross.s...@gmail.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/b11c14bc-5e00-411c-ab56-8bcff7399f5dn%40googlegroups.com.

[Linux_01]

My timezone is IST

[Linux_01]

Going further I want to now disable adding a new column in this regards so that after blocking all columns except column "A" User should not be able add any new columns right /left to column "A".