Created
August 13, 2021 10:54
-
-
Save rapartipoorna/b793e3e3c4923bef93f16f19f6f674cf to your computer and use it in GitHub Desktop.
[ Apps script ] this script captures the sharing permissions and other data f spreadsheet into 2 different sheets in spreadsheet. script should be bounded to spreadsheet
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function myfunction(){ | |
| var pivottables = 0 , charts = 0 | |
| var outputRows = [] , editors = [] ,viewers = [] | |
| var ss = SpreadsheetApp.getActiveSpreadsheet() | |
| var file = DriveApp.getFileById(ss.getId()) | |
| var firstSheet_Header = ["Name of File","Created","Number of People it Shared with", "Editors","Viewers","Number of PivotTables" ,"Number of Charts","Last Updated"] | |
| var secondSheet_Header = ["Name of File","Columns" ,"Rows" ,"Pivot Tables" ,"Charts"] | |
| var firstSheet = ss.insertSheet('Spreadsheet-Overview').appendRow(firstSheet_Header) | |
| var secondSheet = ss.insertSheet('Sheets-Analytics').appendRow(secondSheet_Header) | |
| var sheets = ss.getSheets() | |
| sheets.forEach(function(sheet){ | |
| pivottables = pivottables + sheet.getPivotTables().length | |
| charts = charts + sheet.getCharts().length | |
| }) | |
| getMetadata(file,firstSheet) | |
| // function for capture metadata of files into sheets | |
| function getMetadata(file,firstSheet) { | |
| var last_Updated=file.getLastUpdated() | |
| // var last_updated_by=file | |
| var create_date=file.getDateCreated() | |
| var name=file.getName() | |
| // var owner=file.getOwner().getName(); | |
| var count = file.getEditors().length + file.getViewers().length ; | |
| var shared=(count>0) ? count+' '+'people' : "None" | |
| file.getEditors().forEach(function(editor){ | |
| editors.push(editor.getEmail()) | |
| }) | |
| var editor_emails = editors.toString() | |
| file.getViewers().forEach(function(viewer){ | |
| viewer.push(viewer.getEmail()) | |
| }) | |
| var viewer_emails = viewers.toString(); | |
| firstSheet.appendRow([name,create_date,shared,editor_emails,viewer_emails,pivottables,charts ,last_Updated]) | |
| } | |
| styles(firstSheet) | |
| /* -------------------------------------------------------------- */ | |
| sheets.forEach(sheetsData) | |
| function sheetsData(sheet){ | |
| var sheetName = sheet.getName() | |
| var cols = sheet.getDataRange().getNumColumns() | |
| var rows = sheet.getDataRange().getNumRows() | |
| var pivot = sheet.getPivotTables().length | |
| var charts = sheet.getCharts().length | |
| outputRows.push([sheetName,cols,rows,pivot,charts]) | |
| } | |
| console.log(outputRows) | |
| secondSheet.getRange(2,1,outputRows.length,outputRows[2].length).setValues(outputRows) | |
| styles(secondSheet) | |
| /* ----------------------------------------------------------- */ | |
| // ss.getSheets().forEach(styles) | |
| // function for applying styles to sheets | |
| function styles(sheet_name){ | |
| // sheet_name.getRange().setWrapStrategies() | |
| // sheet_name.getRange(2,7,40000,17).setWrapStrategies(SpreadsheetApp.WrapStrategy.CLIP) | |
| sheet_name.getRange(2,5,40000,3).setHorizontalAlignment("center") | |
| var range=sheet_name.getRange(1,1,1,17).setBackground("yellow") | |
| var style = SpreadsheetApp.newTextStyle() | |
| .setFontSize(10) // defining text styles | |
| .setBold(true) | |
| .build(); | |
| range.setTextStyle(style); | |
| } | |
| /* --------------------------------------------------------------- */ | |
| // function to convert bytes into Kb,Mb,Gb. | |
| // function formatBytes(bytes, decimals = 2) { | |
| // if (bytes === 0) return '0 Bytes'; | |
| // const k = 1024; | |
| // const dm = decimals < 0 ? 0 : decimals; | |
| // const sizes = ['Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB']; | |
| // const i = Math.floor(Math.log(bytes) / Math.log(k)); | |
| // return parseFloat((bytes / Math.pow(k, i)).toFixed(dm)) + ' ' + sizes[i]; | |
| // } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment