Last active
January 31, 2021 05:41
-
-
Save 3panda/7fcc8c5d52bdf359c38edc214d2fbba4 to your computer and use it in GitHub Desktop.
スプレットシートに指定したフォルダのURLからファイル名とURLを取得して一覧にするGAS
This file contains 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 getFileInformation() { | |
//アクティブなシートを取得 | |
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var activeSheet = spreadsheet.getActiveSheet(); | |
var activeSheetName = activeSheet.getName(); | |
console.log(activeSheetName); | |
//対象のディレクトリのURLを取得 | |
// スプレットシートのC1に調べたいDriveのURLを記入しそれを取得 | |
var targetDirectoryURL = activeSheet.getRange('C1').getValue(); | |
console.log(targetDirectoryURL); | |
//idを取得 | |
var myArray = targetDirectoryURL.split(`/`); | |
console.log(myArray); | |
var folderid = myArray[myArray.length-1]; | |
console.log(folderid); | |
// 確認用の親フォルダ名 | |
var parentFolder = DriveApp.getFileById(folderid).getParents(); | |
var parentFolderId = parentFolder.next().getId(); | |
var parentFolderName = DriveApp.getFolderById(parentFolderId).getName(); | |
console.log('parentFolderName;' + parentFolderName); | |
//調査したいフォルダ内のファイルを取得 | |
var folder = DriveApp.getFolderById(folderid); | |
var files= folder.getFiles(); | |
var targetDirectoryName = folder.getName(); | |
console.log(targetDirectoryName); | |
//ファイルとフォルダの情報を入れる配列 | |
var myFolder = []; | |
//ファイル名とファイルURLをmyFolderに格納 | |
while(files.hasNext()){ | |
var file = files.next(); | |
myFolder.push([file.getName(),file.getUrl()]) | |
} | |
console.log(myFolder); | |
// シートに情報を貼り付ける | |
// 対象の親フォルダ名 | |
activeSheet.getRange(2, 3).setValue(parentFolderName); | |
// 対象のフォルダ名 | |
activeSheet.getRange(3, 3).setValue(targetDirectoryName); | |
// ファイル名とURL | |
activeSheet.getRange(6, 2, myFolder.length, myFolder[0].length).setValues(myFolder); | |
console.log(myFolder.length); | |
console.log(myFolder[0].length); | |
// 名前で降順に並び替え | |
var data = activeSheet.getRange(6, 2, myFolder.length, myFolder[0].length); | |
data.sort({column: 2, ascending: true}); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment