{"id":17121,"date":"2025-01-12T11:53:56","date_gmt":"2025-01-12T02:53:56","guid":{"rendered":"https:\/\/84office.jp\/?p=17121"},"modified":"2025-01-13T16:54:32","modified_gmt":"2025-01-13T07:54:32","slug":"file-access-rights-change-setup","status":"publish","type":"post","link":"https:\/\/84office.jp\/en\/file-access-rights-change-setup\/","title":{"rendered":"Effortlessly Manage and Modify Spreadsheet Permissions Using Google Apps Script: Preparation Guide"},"content":{"rendered":"
<\/div>\n

Have you ever struggled with the tedious task of reviewing and modifying sharing settings for individual files, such as spreadsheets or documents?<\/p>\n\n\n\n

This process can become particularly time-consuming and inefficient when dealing with a large number of files.<\/p>\n\n\n\n

If there were a way to manage permissions for multiple files at once, it could significantly reduce the time spent on these tasks.<\/p>\n\n\n\n

In this article, we\u2019ll introduce how to use Google Apps Script to list the current access permissions of your spreadsheet files.<\/p>\n\n\n\n

This method allows you to easily review file-sharing settings, making future access management more efficient.<\/p>\n\n\n\n

Since the process involves two separate scripts, this article will focus on displaying the current permissions for each file. In our next article, we\u2019ll cover how to modify those permissions in bulk.<\/p>\n\n\n

\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\u3042\u308f\u305b\u3066\u8aad\u307f\u305f\u3044<\/span>\n\t\t\t\t\t
\"\"<\/figure><\/div>\t\t\t\t\t
\n\t\t\t\t\t\tGAS\u3092\u4f7f\u3063\u3066\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306e\u30a2\u30af\u30bb\u30b9\u6a29\u9650\u3092\u307e\u3068\u3081\u3066\u7ba1\u7406\u30fb\u5909\u66f4\uff08\u5909\u66f4\u7de8\uff09<\/a>\n\t\t\t\t\t\t\u524d\u56de\u306e\u8a18\u4e8b\u3067\u3001\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u30d5\u30a1\u30a4\u30eb\u306e\u30a2\u30af\u30bb\u30b9\u6a29\u3092\u4e00\u89a7\u5316\u3059\u308b\u65b9\u6cd5\u3092\u3054\u7d39\u4ecb\u3057\u307e\u3057\u305f\u3002 \u4eca\u56de\u306f\u3001\u305d\u306e\u53d6\u5f97\u3057\u305f\u60c5\u5831\u3092\u57fa\u306b\u3057\u3066\u3001\u8907\u6570\u306e\u30d5\u30a1\u30a4\u30eb\u306e\u30a2\u30af\u30bb\u30b9\u6a29\u3092\u4e00\u62ec\u3067\u5909…<\/span>\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/div>\n\n\n

Example Output<\/h2>\n\n\n\n

Current Access Permissions (Columns A\u2013F)<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Permission Change Input Fields (Columns G\u2013M)<\/strong><\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n
    \n
  • Display the current access permissions as a list.<\/li>\n\n\n\n
  • Create input fields for permission changes.<\/li>\n<\/ul>\n\n\n\n

    The spreadsheet will display the following information:<\/p>\n\n\n\n

      \n
    • Folder Name<\/strong> for each file<\/li>\n\n\n\n
    • File Name<\/strong><\/li>\n\n\n\n
    • Access Permissions<\/strong><\/li>\n\n\n\n
    • Email Addresses<\/strong> of the owner, viewers, and editors<\/li>\n<\/ul>\n\n\n\n

      In addition to reviewing access permissions, the spreadsheet will also include input fields for modifying permissions. These fields will be prepared in this article, ensuring that you\u2019re ready to implement permission changes in the next article.<\/p>\n\n\n\n

      Steps<\/h2>\n\n\n\n
      \n
      STEP<\/span><\/div>
      Open the Spreadsheet<\/div>
      \n
      \"\"<\/figure>\n<\/div>\n\n\n

      Open a Google Spreadsheet.
      (Both existing or new files are supported.)<\/p>\n\n\n\n

      Since the script will automatically create all necessary items, no manual input is required.<\/p>\n\n\n\n

        \n
      • The first row headers<\/strong> will be entered by Google Apps Script.<\/li>\n\n\n\n
      • A new sheet named “Access Management”<\/strong> will also be created by the script.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n
        STEP<\/span><\/div>
        Open Apps Script Editor<\/div>
        \n
        \"\"<\/figure>\n<\/div>\n\n\n

        Open a Google Spreadsheet and click on \u201cExtensions<\/strong>\u201d in the menu, then \u201cApps Script<\/strong>\u201d to open the Apps Script editor.<\/p>\n<\/div><\/div>\n\n\n\n

        STEP<\/span><\/div>
        Create the Script<\/strong><\/div>
        \n
        \"\"<\/figure>\n<\/div>\n\n\n

        Since the default function myFunction() {}<\/code> is present, delete it and replace it with the script below.<\/p>\n\n\n\n

        It’s a long script, but there’s a copy button in the top-right corner of the code block.<\/p>\n\n\n\n

        <\/path><\/path><\/svg><\/span>
        function<\/span> <\/span>listFilesAndManageAccess<\/span>() {<\/span><\/span>\n  <\/span>const<\/span> ui <\/span>=<\/span> SpreadsheetApp.<\/span>getUi<\/span>();<\/span><\/span>\n  <\/span>const<\/span> response <\/span>=<\/span> ui.<\/span>prompt<\/span>(<\/span><\/span>\n    <\/span>'Specify a Folder'<\/span>,<\/span><\/span>\n    <\/span>'Enter "All Files in Drive" or specify a folder name or folder ID:'<\/span>,<\/span><\/span>\n    ui.ButtonSet.OK_CANCEL<\/span><\/span>\n  );<\/span><\/span>\n<\/span>\n  <\/span>if<\/span> (response.<\/span>getSelectedButton<\/span>() <\/span>===<\/span> ui.Button.CANCEL) {<\/span><\/span>\n    ui.<\/span>alert<\/span>(<\/span>'Operation cancelled.'<\/span>);<\/span><\/span>\n    <\/span>return<\/span>;<\/span><\/span>\n  }<\/span><\/span>\n<\/span>\n  <\/span>const<\/span> input <\/span>=<\/span> response.<\/span>getResponseText<\/span>().<\/span>trim<\/span>();<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Handle input for "All Files in Drive" or a specific folder<\/span><\/span>\n  <\/span>if<\/span> (input <\/span>===<\/span> <\/span>'All Files in Drive'<\/span>) {<\/span><\/span>\n    <\/span>listAllFiles<\/span>();<\/span><\/span>\n  } <\/span>else<\/span> {<\/span><\/span>\n    <\/span>listFilesInFolder<\/span>(input);<\/span><\/span>\n  }<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nfunction<\/span> <\/span>listAllFiles<\/span>() {<\/span><\/span>\n  <\/span>const<\/span> sheet <\/span>=<\/span> <\/span>setupSheet<\/span>();<\/span><\/span>\n  <\/span>const<\/span> files <\/span>=<\/span> DriveApp.<\/span>getFiles<\/span>();<\/span><\/span>\n  <\/span>let<\/span> row <\/span>=<\/span> <\/span>2<\/span>;<\/span><\/span>\n<\/span>\n  <\/span>while<\/span> (files.<\/span>hasNext<\/span>()) {<\/span><\/span>\n    <\/span>const<\/span> file <\/span>=<\/span> files.<\/span>next<\/span>();<\/span><\/span>\n    <\/span>appendFileDataToSheet<\/span>(sheet, file, row);<\/span><\/span>\n    row<\/span>++<\/span>;<\/span><\/span>\n  }<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nfunction<\/span> <\/span>listFilesInFolder<\/span>(<\/span>folderNameOrId<\/span>) {<\/span><\/span>\n  <\/span>const<\/span> sheet <\/span>=<\/span> <\/span>setupSheet<\/span>();<\/span><\/span>\n  <\/span>let<\/span> folder;<\/span><\/span>\n  <\/span><\/span>\n  <\/span>try<\/span> {<\/span><\/span>\n    folder <\/span>=<\/span> DriveApp.<\/span>getFolderById<\/span>(folderNameOrId);<\/span><\/span>\n  } <\/span>catch<\/span> (e) {<\/span><\/span>\n    <\/span>const<\/span> folders <\/span>=<\/span> DriveApp.<\/span>getFoldersByName<\/span>(folderNameOrId);<\/span><\/span>\n    <\/span>if<\/span> (folders.<\/span>hasNext<\/span>()) {<\/span><\/span>\n      folder <\/span>=<\/span> folders.<\/span>next<\/span>();<\/span><\/span>\n    } <\/span>else<\/span> {<\/span><\/span>\n      SpreadsheetApp.<\/span>getUi<\/span>().<\/span>alert<\/span>(<\/span>'Folder not found.'<\/span>);<\/span><\/span>\n      <\/span>return<\/span>;<\/span><\/span>\n    }<\/span><\/span>\n  }<\/span><\/span>\n  <\/span><\/span>\n  <\/span>const<\/span> files <\/span>=<\/span> folder.<\/span>getFiles<\/span>();<\/span><\/span>\n  <\/span>let<\/span> row <\/span>=<\/span> <\/span>2<\/span>;<\/span><\/span>\n<\/span>\n  <\/span>while<\/span> (files.<\/span>hasNext<\/span>()) {<\/span><\/span>\n    <\/span>const<\/span> file <\/span>=<\/span> files.<\/span>next<\/span>();<\/span><\/span>\n    <\/span>appendFileDataToSheet<\/span>(sheet, file, row);<\/span><\/span>\n    row<\/span>++<\/span>;<\/span><\/span>\n  }<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nfunction<\/span> <\/span>setupSheet<\/span>() {<\/span><\/span>\n  <\/span>const<\/span> sheetName <\/span>=<\/span> <\/span>'Access Management'<\/span>;<\/span><\/span>\n  <\/span>const<\/span> sheet <\/span>=<\/span> SpreadsheetApp.<\/span>getActiveSpreadsheet<\/span>().<\/span>getSheetByName<\/span>(sheetName) <\/span>||<\/span> SpreadsheetApp.<\/span>getActiveSpreadsheet<\/span>().<\/span>insertSheet<\/span>(sheetName);<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Clear and reset the sheet<\/span><\/span>\n  sheet.<\/span>clear<\/span>();<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Set headers<\/span><\/span>\n  <\/span>const<\/span> headers <\/span>=<\/span> [<\/span><\/span>\n    <\/span>'Folder Name'<\/span>, <\/span><\/span>\n    <\/span>'File Name'<\/span>, <\/span><\/span>\n    <\/span>'Current Permissions'<\/span>, <\/span><\/span>\n    <\/span>'Owner'<\/span>, <\/span><\/span>\n    <\/span>'Viewers (Including Commenters)'<\/span>, <\/span><\/span>\n    <\/span>'Editors'<\/span>, <\/span><\/span>\n    <\/span>'Change Target'<\/span>, <\/span><\/span>\n    <\/span>'New Permissions'<\/span>, <\/span><\/span>\n    <\/span>'Remove Viewers'<\/span>, <\/span><\/span>\n    <\/span>'Remove Editors'<\/span>, <\/span><\/span>\n    <\/span>'Add Viewers'<\/span>, <\/span><\/span>\n    <\/span>'Add Editors'<\/span>, <\/span><\/span>\n    <\/span>'Timestamp'<\/span><\/span>\n  ];<\/span><\/span>\n  <\/span><\/span>\n  sheet.<\/span>appendRow<\/span>(headers);<\/span><\/span>\n  <\/span>const<\/span> headerRange <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>1<\/span>, <\/span>1<\/span>, <\/span>1<\/span>, headers.length);<\/span><\/span>\n  headerRange.<\/span>setHorizontalAlignment<\/span>(<\/span>'center'<\/span>);<\/span><\/span>\n  headerRange.<\/span>setVerticalAlignment<\/span>(<\/span>'middle'<\/span>);<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Color-code headers<\/span><\/span>\n  <\/span>const<\/span> greenHeadersRange <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>1<\/span>, <\/span>1<\/span>, <\/span>1<\/span>, <\/span>6<\/span>);<\/span><\/span>\n  <\/span>const<\/span> orangeHeadersRange <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>1<\/span>, <\/span>8<\/span>, <\/span>1<\/span>, <\/span>6<\/span>);<\/span><\/span>\n  <\/span><\/span>\n  greenHeadersRange.<\/span>setBackground<\/span>(<\/span>'#B7E1CD'<\/span>); <\/span>\/\/ Light Green<\/span><\/span>\n  orangeHeadersRange.<\/span>setBackground<\/span>(<\/span>'#FAD7AC'<\/span>); <\/span>\/\/ Light Orange<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Add dropdown options for "New Permissions" column<\/span><\/span>\n  <\/span>const<\/span> permissions <\/span>=<\/span> [<\/span><\/span>\n    <\/span>'Anyone with the link (View)'<\/span>, <\/span><\/span>\n    <\/span>'Anyone with the link (Comment)'<\/span>, <\/span><\/span>\n    <\/span>'Anyone with the link (Edit)'<\/span>, <\/span><\/span>\n    <\/span>'Restricted'<\/span><\/span>\n  ];<\/span><\/span>\n  <\/span>const<\/span> validation <\/span>=<\/span> SpreadsheetApp.<\/span>newDataValidation<\/span>().<\/span>requireValueInList<\/span>(permissions).<\/span>build<\/span>();<\/span><\/span>\n  sheet.<\/span>getRange<\/span>(<\/span>2<\/span>, <\/span>8<\/span>, sheet.<\/span>getMaxRows<\/span>() <\/span>-<\/span> <\/span>1<\/span>).<\/span>setDataValidation<\/span>(validation);<\/span><\/span>\n  <\/span><\/span>\n  <\/span>return<\/span> sheet;<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nfunction<\/span> <\/span>appendFileDataToSheet<\/span>(<\/span>sheet<\/span>, <\/span>file<\/span>, <\/span>row<\/span>) {<\/span><\/span>\n  <\/span>const<\/span> folderName <\/span>=<\/span> <\/span>getFolderName<\/span>(file);<\/span><\/span>\n  <\/span>const<\/span> fileName <\/span>=<\/span> file.<\/span>getName<\/span>();<\/span><\/span>\n  <\/span>const<\/span> fileLink <\/span>=<\/span> file.<\/span>getUrl<\/span>();<\/span><\/span>\n  <\/span>const<\/span> currentPermission <\/span>=<\/span> <\/span>translatePermission<\/span>(file.<\/span>getSharingAccess<\/span>(), file.<\/span>getSharingPermission<\/span>());<\/span><\/span>\n<\/span>\n  <\/span>\/\/ Append file data with a hyperlink for the file name<\/span><\/span>\n  sheet.<\/span>getRange<\/span>(row, <\/span>1<\/span>).<\/span>setValue<\/span>(folderName);<\/span><\/span>\n  sheet.<\/span>getRange<\/span>(row, <\/span>2<\/span>).<\/span>setFormula<\/span>(<\/span>`=HYPERLINK("<\/span>${<\/span>fileLink<\/span>}<\/span>", "<\/span>${<\/span>fileName<\/span>}<\/span>")`<\/span>);<\/span><\/span>\n  sheet.<\/span>getRange<\/span>(row, <\/span>3<\/span>).<\/span>setValue<\/span>(currentPermission);<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Retrieve member details<\/span><\/span>\n  <\/span>const<\/span> viewers <\/span>=<\/span> file.<\/span>getViewers<\/span>().<\/span>map<\/span>(<\/span>user<\/span> <\/span>=><\/span> user.<\/span>getEmail<\/span>());<\/span><\/span>\n  <\/span>const<\/span> editors <\/span>=<\/span> file.<\/span>getEditors<\/span>().<\/span>map<\/span>(<\/span>user<\/span> <\/span>=><\/span> user.<\/span>getEmail<\/span>());<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Safely get the owner's email<\/span><\/span>\n  <\/span>const<\/span> owner <\/span>=<\/span> file.<\/span>getOwner<\/span>() <\/span>?<\/span> file.<\/span>getOwner<\/span>().<\/span>getEmail<\/span>() <\/span>:<\/span> <\/span>'No Owner'<\/span>;<\/span><\/span>\n<\/span>\n  <\/span>\/\/ Populate member details in respective columns<\/span><\/span>\n  sheet.<\/span>getRange<\/span>(row, <\/span>4<\/span>).<\/span>setValue<\/span>(owner);<\/span><\/span>\n  sheet.<\/span>getRange<\/span>(row, <\/span>5<\/span>).<\/span>setValue<\/span>(viewers.<\/span>join<\/span>(<\/span>', '<\/span>));<\/span><\/span>\n  sheet.<\/span>getRange<\/span>(row, <\/span>6<\/span>).<\/span>setValue<\/span>(editors.<\/span>join<\/span>(<\/span>', '<\/span>));<\/span><\/span>\n  sheet.<\/span>getRange<\/span>(row, <\/span>7<\/span>).<\/span>insertCheckboxes<\/span>(); <\/span>\/\/ Add checkboxes for changes<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nfunction<\/span> <\/span>getFolderName<\/span>(<\/span>file<\/span>) {<\/span><\/span>\n  <\/span>const<\/span> parents <\/span>=<\/span> file.<\/span>getParents<\/span>();<\/span><\/span>\n  <\/span>return<\/span> parents.<\/span>hasNext<\/span>() <\/span>?<\/span> parents.<\/span>next<\/span>().<\/span>getName<\/span>() <\/span>:<\/span> <\/span>'Root'<\/span>;<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nfunction<\/span> <\/span>translatePermission<\/span>(<\/span>access<\/span>, <\/span>permission<\/span>) {<\/span><\/span>\n  <\/span>const<\/span> accessMap <\/span>=<\/span> {<\/span><\/span>\n    [DriveApp.Access.ANYONE]: <\/span>'Anyone'<\/span>,<\/span><\/span>\n    [DriveApp.Access.ANYONE_WITH_LINK]: <\/span>'Anyone with the link'<\/span>,<\/span><\/span>\n    [DriveApp.Access.PRIVATE]: <\/span>'Restricted'<\/span><\/span>\n  };<\/span><\/span>\n  <\/span>const<\/span> permissionMap <\/span>=<\/span> {<\/span><\/span>\n    [DriveApp.Permission.VIEW]: <\/span>'View'<\/span>,<\/span><\/span>\n    [DriveApp.Permission.COMMENT]: <\/span>'Comment'<\/span>,<\/span><\/span>\n    [DriveApp.Permission.EDIT]: <\/span>'Edit'<\/span><\/span>\n  };<\/span><\/span>\n<\/span>\n  <\/span>if<\/span> (access <\/span>===<\/span> DriveApp.Access.PRIVATE) {<\/span><\/span>\n    <\/span>return<\/span> accessMap[access];<\/span><\/span>\n  } <\/span>else<\/span> {<\/span><\/span>\n    <\/span>return<\/span> <\/span>`<\/span>${<\/span>accessMap[access] <\/span>||<\/span> <\/span>'Unknown'<\/span>}<\/span> (<\/span>${<\/span>permissionMap[permission] <\/span>||<\/span> <\/span>'Unknown'<\/span>}<\/span>)`<\/span>;<\/span><\/span>\n  }<\/span><\/span>\n}<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
        \n
        Script Description<\/span><\/i><\/i><\/span><\/summary>
        \n
        \n
        Main Function:listFilesAndManageAccess()<\/code><\/dt>\n\n\n\n
        \n
        <\/path><\/path><\/svg><\/span>
        function<\/span> <\/span>listFilesAndManageAccess<\/span>() {<\/span><\/span>\n  <\/span>const<\/span> ui <\/span>=<\/span> SpreadsheetApp.<\/span>getUi<\/span>();<\/span><\/span>\n  <\/span>const<\/span> response <\/span>=<\/span> ui.<\/span>prompt<\/span>(<\/span><\/span>\n    <\/span>'Specify a Folder'<\/span>,<\/span><\/span>\n    <\/span>'Enter "All Files in Drive" or specify a folder name or folder ID:'<\/span>,<\/span><\/span>\n    ui.ButtonSet.OK_CANCEL<\/span><\/span>\n  );<\/span><\/span>\n<\/span>\n  <\/span>if<\/span> (response.<\/span>getSelectedButton<\/span>() <\/span>===<\/span> ui.Button.CANCEL) {<\/span><\/span>\n    ui.<\/span>alert<\/span>(<\/span>'Operation cancelled.'<\/span>);<\/span><\/span>\n    <\/span>return<\/span>;<\/span><\/span>\n  }<\/span><\/span>\n<\/span>\n  <\/span>const<\/span> input <\/span>=<\/span> response.<\/span>getResponseText<\/span>().<\/span>trim<\/span>();<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Handle input for "All Files in Drive" or a specific folder<\/span><\/span>\n  <\/span>if<\/span> (input <\/span>===<\/span> <\/span>'All Files in Drive'<\/span>) {<\/span><\/span>\n    <\/span>listAllFiles<\/span>();<\/span><\/span>\n  } <\/span>else<\/span> {<\/span><\/span>\n    <\/span>listFilesInFolder<\/span>(input);<\/span><\/span>\n  }<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
          \n
        • When the script is executed, a popup will appear, allowing you to specify either “All Files in Drive”<\/strong> or a specific folder name or folder ID.<\/li>\n\n\n\n
        • Your selection will determine the scope of files to be processed in the next steps.<\/li>\n<\/ul>\n<\/dd>\n\n\n\n
          List All Files in Drive:listAllFiles()<\/code><\/dt>\n\n\n\n
          \n
          <\/path><\/path><\/svg><\/span>
          function<\/span> <\/span>listAllFiles<\/span>() {<\/span><\/span>\n  <\/span>const<\/span> sheet <\/span>=<\/span> <\/span>setupSheet<\/span>();<\/span><\/span>\n  <\/span>const<\/span> files <\/span>=<\/span> DriveApp.<\/span>getFiles<\/span>();<\/span><\/span>\n  <\/span>let<\/span> row <\/span>=<\/span> <\/span>2<\/span>;<\/span><\/span>\n<\/span>\n  <\/span>while<\/span> (files.<\/span>hasNext<\/span>()) {<\/span><\/span>\n    <\/span>const<\/span> file <\/span>=<\/span> files.<\/span>next<\/span>();<\/span><\/span>\n    <\/span>appendFileDataToSheet<\/span>(sheet, file, row);<\/span><\/span>\n    row<\/span>++<\/span>;<\/span><\/span>\n  }<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n