I've been looking through the whole internet looking for a How to or a code to list my Shared Drive files. At the beggining I found one that seemed to work but after further testing it had major flaws with the way it scanned folders.
Now I've apparently found again some code that I had to adapt to use in order to make it work with Shared Drives but I managed to do it. My only issue is that it only seems to to list the files inside the first folder/subfolder it finds and doesn't loop. My issue is that it is too fast for me to diagnose and after a whole weekend of setup of my spreadsheet I don't have the time nor the knowledge to fix it myself.
Here is the code that I adapted to work with Shared Drives:
function ListarTodo() {
/* Adapted from Adapted Code written by @Andres Duarte and Adapted by @Eric Aya in this link:
https://stackoverflow.com/a/63267959/18311037
*/
// Lista todos los archivos de una carpeta y de sus sub carpetas, y toma el nombre de la carpeta a analizar del nombre de la hoja activa.
// List all files and sub-folders in a single folder on Google Drive, and get the name of the activesheet to know the folder desired.
var parentFolder = DriveApp.getFolderById("INSERT YOUR FOLDER ID HERE");
// Declaramos la hoja // declare this sheet
var sheet = SpreadsheetApp.getActive().getSheetByName('INSERT YOUR SHEET NAME HERE');
// Borramos los datos de la hoja // clear any existing contents
sheet.clear();
// Agregamos una linea con los titulos // append a header row
sheet.appendRow(["Carpeta","Nombre Archivo", "Fecha ultima modificacion", "Tamaño MB", "URL", "ID", "Descripción", "Tipo archivo"]);
// getFoldersByName = obtener una coleccion de todas las carpetas en la unidad Drive que tienen el nombre buscado "foldername".
// folders es un "iterador de carpetas" pero hay solo una carpeta para llamar por el nombre, por eso tiene un solo valor (next)
// getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
// folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
var folders = parentFolder.getFolders();
var foldersnext = folders.next();
var lintotal = 2;
//Iniciamos la funcion recursiva // Initiate recursive function
lintotal = SubCarpetas(foldersnext, parentFolder.getName(), lintotal);
}
function SubCarpetas(folder, path, cantlineas) {
cantlineas = ListarArchivos(folder, path, cantlineas);
var subfolders = folder.getFolders();
while (subfolders.hasNext()) {
var mysubfolders = subfolders.next();
var mysubfolderName = mysubfolders.getName();
var newpath = "";
newpath = path "/" mysubfolderName;
cantlineas = SubCarpetas(mysubfolders, newpath, cantlineas);
}
return(cantlineas)
}
function ListarArchivos(mifoldersnext, mipath, milintotal) {
var datos = []; //array temporal que vamos a usar para grabar en la hoja
var files = []; //array con todos los archivos que encontramos en la carpeta que estamos evaluando
var file = []; //array que usamos para volcar los datos de cada archivo antes de guardarlo
var total = 0;
var sheet = SpreadsheetApp.getActiveSheet();
var myfiles = mifoldersnext.getFiles();
// Creamos un array con los datos de cada archivo y guardamos el total de archivos
while (myfiles.hasNext()) {
files.push(myfiles.next());
total ;
}
//ordenamos el array por nombre de archivo alfabeticamente //sorts the files array by file names alphabetically
files = files.sort(function(a, b){
var aName = a.getName().toUpperCase();
var bName = b.getName().toUpperCase();
return aName.localeCompare(bName);
});
////
var vuelta = 0;
var bulk = 10; //Definimos la cantidad de lineas a grabar cada vez, en la hoja de la planilla GoogleDoc
var linea = milintotal; //definimos en que linea vamos a grabar en la planilla
for (var i = 0; i < files.length; i ) { //recorremos el array de archivos y formateamos la informacion que necesitamos para nuestra planilla
file = files[i];
var fname = file.getName(); //nombre del archivo
var fdate = file.getLastUpdated(); //fecha y hora ultima modificacion
var fsize = file.getSize(); //tamaño del archivo, lo pasamos de byte a Kbyte y luego a Mb
fsize = fsize.toFixed(2); //lo formateamos a dos decimales
var furl = file.getUrl(); //url del archivo
var fid = file.getId(); //id del archivo
var fdesc = file.getDescription(); //descripcion
var ftype = file.getMimeType(); //tipo de archivo
datos[vuelta] = [mipath " (" total ")", fname, fdate, fsize, furl, fid, fdesc, ftype]; //ponemos todo dentro de un array temporal
vuelta ;
if (vuelta == bulk) {//cuando alcanza la cantidad definida, guarda este array con 10 lineas y lo vacía
linea = milintotal;
// Logger.log("linea = " linea); //DEBUG
// Logger.log("vuelta = " vuelta); //DEBUG
// Logger.log("total = " total); //DEBUG
// Logger.log("lintotal = " milintotal); //DEBUG
// Logger.log("registros en datos = " datos.length); //DEBUG
// Logger.log("data = " datos); //DEBUG
sheet.getRange(linea, 1, bulk,8).setValues(datos); //guardamos los datos del array temporal en la hoja
SpreadsheetApp.flush(); //forzamos que aparezcan los datos en la hoja - sin esto los datos no aparecen hasta terminar (genera mucha impaciencia)
milintotal = milintotal vuelta;
datos = []; //vaciamos el array temporal
vuelta = 0;
}
}
if (datos.length>0) {//Al salir del bucle grabamos lo que haya quedado en el array datos
linea = milintotal;
// Logger.log("linea = " linea); //DEBUG
// Logger.log("vuelta = " vuelta); //DEBUG
// Logger.log("total = " total); //DEBUG
// Logger.log("lintotal = " milintotal); //DEBUG
// Logger.log("registros en datos = " datos.length); //DEBUG
// Logger.log("data = " datos); //DEBUG
sheet.getRange(linea, 1, datos.length,8).setValues(datos);
SpreadsheetApp.flush(); //ansiolítico
milintotal = milintotal datos.length;
datos = [];
vuelta = 0;
}
return (milintotal)
}
And here is the LINK to the original comment/code.
I'm new to StackOverflow so I'll do my best to mark the solutions and give feedback on the answers.
CodePudding user response：
From your comment, I believe your goal is as follows.
- You want to retrieve the file and folder list from the specific folder.
- You want to retrieve the metadata of "Name", "size", "added date", "modification date", "url".
- You want to achieve this using Google Apps Script.
- You want to reduce the process cost of the script for achieving this.
When I saw your showing script, setValues is used in the loop. In this case, the process cost becomes high. So, in this case, how about the following sample script?
In this sample script, a Google Apps Script library is used. Ref I created this library for retrieving the file and folder list with the low process cost using Google Apps Script.
Usage:
1. Install Google Apps Script library.
You can see the method for installing the library at here.
2. Enable Drive API.
This library uses Drive API. So please enable Drive API at Advanced Google services.
3. Sample script.
function sample() {
const folderId = "###"; // Please set the top folder ID.
// 1. Retrieve file and folder list under the specific folder.
const { files } = FilesApp.createTree(folderId, null, "files(name,modifiedTime,createdTime,size,quotaBytesUsed,webViewLink,description)");
// 2. Parse the list and create an array for putting to Spreadsheet.
if (files.length == 0) return;
const header = ["Name", "size", "added date", "modification date", "url", "description"];
const values = [header, ...files.flatMap(({ filesInFolder }) => filesInFolder.map(e => [e.name, Number(e.quotaBytesUsed || e.size), new Date(e.createdTime), new Date(e.modifiedTime), e.webViewLink, e.description]))];
// 3. Put the values to the Spreadsheet.
const sheetName = "Sheet2"; // Please set the sheet nane.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
sheet.clearContents();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
- In this script,
folderIdcan use both your Drive and the shared Drive. - When this script is run, the file and folder list is retrieved from
folderIdand put the values of"Name", "size", "added date", "modification date", "url"to the Spreadsheet.