Need a Script that can Count Files in Google Drive Folders
Sagar Rathore
I need a reliable Google Sheets script that counts files in multiple Google Drive folders.
Requirements:
- Handle all types of links or folder IDs in column E (plain URLs, rich-text links, or raw IDs).
- Count files recursively within each folder and its specified subfolders.
- Process data in batches (e.g., 50 rows per run) to avoid execution timeouts.
- Automatically continue running batches until the entire sheet is processed.
- Output the counts starting from column F onward.
- Automatically remove triggers once processing is complete.
- Include logging to help with troubleshooting.
Goal:
I want a script that takes a folder link (or ID) and counts all items inside the specified subfolders. The count should stay updated as new files are added, and the script should handle future changes automatically.
Current Issue:
The script sometimes fails to read files — I can see files inside the folders, but the script returns a count of zero. I need the solution to be accurate and reliable.if it done one whole run than in second run it wont update that old data as folder file updated or removed.
Mario Rossi
- accepts folder URLs, rich-text links or raw IDs from column E,
- resolves the ID robustly,
- counts files recursively (configurable depth or full recursion),
- processes rows in batches (configurable, e.g. 50) and continues automatically via time-based trigger until the sheet is done,
- writes counts starting at column F,
- refreshes counts on subsequent runs (overwrites old results so updates/removals are reflected),
- removes the trigger when processing completes,
- logs progress and errors for troubleshooting.
- Do you want a single total count per row (all files in folder+subfolders) or counts broken out by subfolder levels or file type?
- Which sheet/tab contains the data and does the header row start at row 1? (I can detect but confirmation helps.)
- Do you want to include files in Google Shared Drives? The script needs DriveApp access and appropriate permissions.
- Confirm batch size you prefer (default 50) and whether you want the script to run on a schedule as well as via trigger continuation.
- Please grant edit access to the sheet or share a small sample if you prefer I test against real rows.
Sagar Rathore
Hi Mario Rossi — thanks for the detailed response. My requirement is slightly more specific based on my sheet structure.
Each row contains a main Google Drive folder link in column E.
Inside that folder there are multiple subfolders (e.g. Folder 1, Folder 2, Folder 3 … Folder 7, Others)
I need the script to:
• Recursively scan the main folder
• Count files inside each specific subfolder category
• Put counts in columns F onward (Folder 1 → Folder 7 → Others)
• If files are added later, counts should update correctly
• Ignore trashed files
• Ignore shortcuts (count actual files only)
• Count all file types
• Work in batches (50 rows per run)
• Automatically continue until complete
• Remove trigger when finished
• Log progress for troubleshooting
• Provide stable counts (currently they change each run)
• Allow periodic updates (e.g. hourly) so counts stay updated when files changeFolder classification logic:
• If file is inside subfolder named "Folder 1" → count in column F
• If inside "Folder 2" → column G
• … up to Folder 7 so onSheet name: use the existing sheet (first tab is fine)
Approximate size: around 8k–10k files total (may grow later).
Main requirement: counts must be consistent and reliable. Currently I get different values on each run, so stability is very important.
The sample sheet I shared reflects the structure I need.
Thanks — looking forward to the script.
Mario Rossi
- Read folder links/IDs from column E (row 2 down; row 1 header).
- Recursively scan each main folder, count actual files (ignore trashed & shortcuts) and classify by the immediate subfolder name path: “Folder 1” → col F, … “Folder 7” → next columns, anything else → “Others”.
- Use the Drive Advanced API (Files.list with pagination) to ensure stable, complete results and avoid duplicates; use a Set of file IDs.
- Process rows in batches (default 50), create a time-based continuation trigger until all rows processed, then remove the trigger.
- Overwrite previous counts so updates/removals reflect correctly.
- Log progress and errors to Stackdriver/Execution logs and write a simple progress column (optional).
- Support periodic scheduled runs (hourly) and manual runs; counts will be deterministic each run.
- Handle ~8–10k files efficiently; scales as needed.
- Shared Drives: do you want to include files in Shared Drives? (If yes, the script will set supportsAllDrives=true and includeItemsFromAllDrives=true.)
- Exact mapping for columns: confirm Folder 1 → col F, Folder 2 → col G, … Folder 7 → col L, Others → col M (this matches your description unless you prefer different columns).
- Depth: classification is based on whether a file’s path contains a subfolder named exactly “Folder X” anywhere under the main folder (I’ll match by folder name, case-insensitive). Confirm this, or if you want only top-level immediate children named “Folder X”.
- Do you want a per-row log cell with timestamp of last scan? (useful for troubleshooting)
- If you confirm 1–4 above I will post the complete Apps Script code, installation/authorization steps, and instructions to run and to grant the script access to the sheet (or you can share a small sample sheet with edit permissions and I can test).
- If you prefer, I can default to: include Shared Drives = no, columns as mapped in item 2, match “Folder X” anywhere in the path (case-insensitive), and add a last-run timestamp.
DimuDesigns
Just so you know, "Mario Rossi" is an AI Chat bot that uses ChatGPT.
Its recommendations will likely work for many use cases but I've found that some LLMs tend to oversell Apps Scripts capabilities without taking into account its limitations and constraints.
GAS is bound by several service quotas that make it ill-suited for applications that need to operate at scale so if you expect to be processing a large volume of files you may run into issues.
So keep that in mind. And be sure to properly review any AI-generated scripts before you use them.
Mario Rossi
Ben
Hi Sagar,
I implemented this using Google’s stable APIs directly, not Apps Script. So it is not limited by Apps Script execution time, trigger behavior, or quota constraints.
It can handle the workflow you described:
- read folder links / IDs from column E
- count files recursively by Folder 1 to Folder 7 and Others
- ignore trashed files
- ignore shortcuts
- write counts from column F onward
- keep counts stable and support incremental updates when folders change
I also ran a working demo on your sheet structure. Please check this demo:
Demo sheet
If this is still useful for you, I can package it into a clean working version for your sheet.
Also, what would be a reasonable budget for you if this saves you from the current manual checking and unreliable counts?
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdqZzoeokCgcrF7y8E%3DSmGj8AEdh7ZP_1cV6MUT66gzGGg%40mail.gmail.com.
With kind regards,
Ben
Mario Rossi
- Would you include installation/deployment steps and a small README for incremental updates?
- What permissions/service accounts does it require (Drive scopes, domain-wide, etc.)?
- Estimated delivery time once agreed.
- Rough budget range you expect (or a couple of pricing options: basic install vs. ongoing support/maintenance).
Mario
Sagar Rathore
Hi Ben,
Thanks for sharing your approach and the demo, it looks really solid.
Right now I’m trying to get this working reliably within Google Apps Script so everything stays simple and self-contained. Before I go down the route of a paid or external solution, I just want to understand a bit more.
Do you think the issues I’m seeing, like inconsistent counts, zero results, and rows not updating, can be fully fixed within Apps Script if done properly using the Drive API and pagination? Or did you run into limitations that made it unreliable in your case?
It would really help if you could share what didn’t work well with Apps Script in your experience, and what your solution does better in terms of accuracy, scalability, and maintenance.
At the moment I’m prioritizing a free Apps Script solution, and I’d only consider a paid setup if this genuinely can’t be made reliable.
That said, if you have a rough estimate for a basic packaged version without ongoing support, feel free to share.
Thanks again, appreciate your time.
Regards
Sagar Rathore
Hi Mario,
Thanks — your proposed approach looks good and aligned with what I need. Please go ahead with the implementation using your default assumptions, with the following confirmations:
Configuration:
• Include Shared Drives: Yes
• Column mapping:
Folder 1 → F, Folder 2 → G, Folder 3 → H, Folder 4 → I, Folder 5 → J, Folder 6 → K, Folder 7 → L, Others → M
• Classification: Match “Folder X” anywhere in the path (case-insensitive)
• Files directly inside the main folder should count as “Others”
• Sheet structure: Header in row 1, data starting from row 2
• Add a last-run timestamp column for each row
File Handling Rules:
• Ignore trashed files
• Ignore shortcuts
• Count all file types
Reliability & Accuracy Requirements:
• The script must always produce deterministic counts (same result if no changes occurred)
• It should never return 0 unless the folder is actually empty
• Ensure no duplicate counting (use file ID deduplication)
• Handle pagination correctly to avoid missing files in large folders
• Fully overwrite previous results every run so additions/removals are reflected
Robustness Enhancements:
• Implement a retry mechanism in case Drive API pagination fails mid-run
• Maintain a checkpoint (e.g., last processed page token or folder) so partial runs can resume instead of restarting
• If a folder is invalid or inaccessible, log it clearly and skip it instead of failing the entire batch
Logging Requirements:
Please include clear logging for:
• Row number being processed
• Resolved folder ID
• Total files scanned
• Final counts per category
• Any errors or skipped cases
Performance & Execution:
• Batch size: 50 rows per run
• Use continuation triggers until processing is complete
• Automatically remove the trigger after full completion
• Support hourly scheduled refresh without breaking counts
My current dataset is around 8k–10k files and growing, so stability and consistency are the most important priorities.
You can proceed without needing access to my sheet — just ensure the script is easy to plug in and configure.
Please share:
• Full Apps Script code (ready to paste)
• Setup steps (including enabling Advanced Drive API if used)
• Instructions for running and debugging logs
Looking forward to the implementation.
Thanks
DimuDesigns
Ben
Yes, a Google Apps Script version can be improved a lot if it is built around the Advanced Drive API, proper pagination, file ID deduplication, overwrite on refresh logic, retries, and resumable batching.
So in principle, the issues you are seeing now, such as inconsistent counts, false zeroes, and rows not refreshing correctly, are not necessarily inherent to Apps Script. A careful implementation can make it much more stable than what you have now.
That said, with the dataset size you mentioned, around 8k to 10k files and growing, Apps Script still has some structural limits:
execution time limits per run,
trigger chaining complexity,
quota sensitivity during large recursive scans,
and more maintenance overhead as the dataset grows.
So my honest view is:
Apps Script can probably be made workable,
but keeping it consistently reliable over time will be harder at your scale, especially with hourly refreshes and continued growth.
The main advantage of the direct API based approach is not just counting itself. It is stability, deterministic results, and cleaner incremental updates when folders change.
For pricing, I would rather send a private estimate by email.
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/fde91ada-0872-447f-a533-63a8e5cf654fn%40googlegroups.com.
Kildere S Irineu
* =========================================================
* Google Apps Script - Contagem recursiva de arquivos no Drive
* =========================================================
*
* Requisitos atendidos:
* - Lê URL, rich text link ou ID cru na coluna E
* - Conta arquivos recursivamente
* - Classifica por Folder 1..Folder 7 e Others
* - Ignora trashed e shortcuts
* - Processa em lotes
* - Continua automaticamente com trigger
* - Sobrescreve resultados antigos
* - Suporta Shared Drives
* - Registra logs
*
* IMPORTANTE:
* 1) Ative o Advanced Drive Service:
* Services > + > Drive API
* 2) Se o projeto usar Cloud Project padrão, normalmente basta ativar o serviço.
* Se usar projeto GCP padrão/customizado, também confirme a Drive API no Google Cloud.
*/
/** =========================
* Configuração principal
* ========================= */
const CONFIG = {
SHEET_NAME: '', // vazio = primeira aba
HEADER_ROW: 1,
DATA_START_ROW: 2,
BATCH_SIZE: 50,
SOURCE_COL: 5, // E
OUTPUT_START_COL: 6, // F
OUTPUT_COLS_COUNT: 8, // F:M
LAST_RUN_COL: 14, // N
STATUS_COL: 15, // O
CONTINUATION_FUNCTION: 'processFolderCountsBatch',
HOURLY_FUNCTION: 'startFullRefresh',
CONTINUATION_TRIGGER_DELAY_MS: 60 * 1000, // 1 min
LOCK_WAIT_MS: 20 * 1000,
INCLUDE_SHARED_DRIVES: true,
CASE_INSENSITIVE_MATCH: true,
// nomes exatos das categorias
BUCKET_NAMES: [
'Folder 1',
'Folder 2',
'Folder 3',
'Folder 4',
'Folder 5',
'Folder 6',
'Folder 7',
'Others'
],
// propriedades de estado
PROP_CURSOR_ROW: 'DRIVE_COUNT_CURSOR_ROW',
PROP_IS_RUNNING: 'DRIVE_COUNT_IS_RUNNING',
// segurança contra loops longos
MAX_API_RETRIES: 4,
API_RETRY_BASE_MS: 1000,
// paginação Drive API
PAGE_SIZE: 1000
};
/**
* Adiciona menu customizado na planilha.
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Drive Counter')
.addItem('1. Iniciar atualização completa', 'startFullRefresh')
.addItem('2. Continuar processamento', 'processFolderCountsBatch')
.addSeparator()
.addItem('3. Instalar atualização horária', 'installHourlyRefresh')
.addItem('4. Remover trigger horário', 'removeHourlyRefresh')
.addItem('5. Remover triggers de continuação', 'removeContinuationTriggers')
.addSeparator()
.addItem('6. Resetar estado', 'resetProcessingState')
.addToUi();
}
/**
* Inicia o reprocessamento completo a partir da linha 2.
* Sobrescreve contagens antigas para refletir alterações no Drive.
*/
function startFullRefresh() {
const lock = LockService.getScriptLock();
lock.waitLock(CONFIG.LOCK_WAIT_MS);
try {
const props = PropertiesService.getScriptProperties();
// marca início na primeira linha de dados
props.setProperty(CONFIG.PROP_CURSOR_ROW, String(CONFIG.DATA_START_ROW));
props.setProperty(CONFIG.PROP_IS_RUNNING, 'true');
// remove triggers antigos de continuação para evitar duplicação
removeContinuationTriggers();
console.log('[startFullRefresh] Processamento completo iniciado.');
// processa a primeira leva imediatamente
processFolderCountsBatch();
} finally {
lock.releaseLock();
}
}
/**
* Processa um lote de linhas.
* Se restarem linhas, agenda próxima continuação.
* Se terminar, remove trigger de continuação.
*/
function processFolderCountsBatch() {
const lock = LockService.getScriptLock();
const gotLock = lock.tryLock(CONFIG.LOCK_WAIT_MS);
if (!gotLock) {
console.log('[processFolderCountsBatch] Não foi possível obter lock. Saindo.');
return;
}
try {
const props = PropertiesService.getScriptProperties();
const sheet = getTargetSheet_();
const lastRow = sheet.getLastRow();
if (lastRow < CONFIG.DATA_START_ROW) {
console.log('[processFolderCountsBatch] Não há dados para processar.');
finishProcessing_();
return;
}
let cursorRow = Number(props.getProperty(CONFIG.PROP_CURSOR_ROW) || CONFIG.DATA_START_ROW);
if (cursorRow > lastRow) {
console.log('[processFolderCountsBatch] Cursor já passou do fim. Finalizando.');
finishProcessing_();
return;
}
const endRow = Math.min(cursorRow + CONFIG.BATCH_SIZE - 1, lastRow);
const numRows = endRow - cursorRow + 1;
console.log(`[processFolderCountsBatch] Processando linhas ${cursorRow} até ${endRow}.`);
// lê valores da coluna E e rich text da coluna E
const sourceRange = sheet.getRange(cursorRow, CONFIG.SOURCE_COL, numRows, 1);
const displayValues = sourceRange.getDisplayValues();
const richTextValues = sourceRange.getRichTextValues();
const outputValues = [];
const lastRunValues = [];
const statusValues = [];
for (let i = 0; i < numRows; i++) {
const rowNumber = cursorRow + i;
const cellDisplayValue = displayValues[i][0];
const richTextValue = richTextValues[i][0];
try {
const folderId = resolveFolderIdFromCell_(cellDisplayValue, richTextValue);
if (!folderId) {
outputValues.push(makeZeroOutputRow_());
lastRunValues.push([new Date()]);
statusValues.push(['Sem ID/URL válido']);
console.log(`[row ${rowNumber}] Nenhum ID/URL válido encontrado.`);
continue;
}
console.log(`[row ${rowNumber}] Folder ID resolvido: ${folderId}`);
const counts = countFilesRecursivelyByBuckets_(folderId);
outputValues.push([
counts['Folder 1'] || 0,
counts['Folder 2'] || 0,
counts['Folder 3'] || 0,
counts['Folder 4'] || 0,
counts['Folder 5'] || 0,
counts['Folder 6'] || 0,
counts['Folder 7'] || 0,
counts['Others'] || 0
]);
lastRunValues.push([new Date()]);
statusValues.push([`OK | scanned=${counts.__scanned || 0}`]);
console.log(
`[row ${rowNumber}] Final counts: ` +
JSON.stringify({
folderId,
scanned: counts.__scanned || 0,
folder1: counts['Folder 1'] || 0,
folder2: counts['Folder 2'] || 0,
folder3: counts['Folder 3'] || 0,
folder4: counts['Folder 4'] || 0,
folder5: counts['Folder 5'] || 0,
folder6: counts['Folder 6'] || 0,
folder7: counts['Folder 7'] || 0,
others: counts['Others'] || 0
})
);
} catch (err) {
outputValues.push(makeZeroOutputRow_());
lastRunValues.push([new Date()]);
statusValues.push([`ERRO: ${truncate_(String(err && err.message ? err.message : err), 200)}`]);
console.log(`[row ${rowNumber}] ERRO: ${err && err.stack ? err.stack : err}`);
}
}
// escreve em lote
sheet
.getRange(cursorRow, CONFIG.OUTPUT_START_COL, outputValues.length, CONFIG.OUTPUT_COLS_COUNT)
.setValues(outputValues);
sheet
.getRange(cursorRow, CONFIG.LAST_RUN_COL, lastRunValues.length, 1)
.setValues(lastRunValues);
sheet
.getRange(cursorRow, CONFIG.STATUS_COL, statusValues.length, 1)
.setValues(statusValues);
// avança cursor
const nextRow = endRow + 1;
props.setProperty(CONFIG.PROP_CURSOR_ROW, String(nextRow));
if (nextRow <= lastRow) {
scheduleContinuationTrigger_();
console.log(`[processFolderCountsBatch] Próximo lote agendado a partir da linha ${nextRow}.`);
} else {
finishProcessing_();
console.log('[processFolderCountsBatch] Processamento concluído.');
}
} finally {
lock.releaseLock();
}
}
/**
* Instala trigger horário para atualizar tudo periodicamente.
* Mantém apenas um trigger horário dessa função.
*/
function installHourlyRefresh() {
removeHourlyRefresh();
ScriptApp.newTrigger(CONFIG.HOURLY_FUNCTION)
.timeBased()
.everyHours(1)
.create();
console.log('[installHourlyRefresh] Trigger horário instalado.');
}
/**
* Remove trigger horário.
*/
function removeHourlyRefresh() {
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach(trigger => {
if (trigger.getHandlerFunction() === CONFIG.HOURLY_FUNCTION) {
ScriptApp.deleteTrigger(trigger);
}
});
console.log('[removeHourlyRefresh] Trigger horário removido, se existia.');
}
/**
* Remove triggers de continuação.
*/
function removeContinuationTriggers() {
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach(trigger => {
if (trigger.getHandlerFunction() === CONFIG.CONTINUATION_FUNCTION) {
ScriptApp.deleteTrigger(trigger);
}
});
console.log('[removeContinuationTriggers] Triggers de continuação removidos, se existiam.');
}
/**
* Limpa propriedades de estado.
*/
function resetProcessingState() {
const props = PropertiesService.getScriptProperties();
props.deleteProperty(CONFIG.PROP_CURSOR_ROW);
props.deleteProperty(CONFIG.PROP_IS_RUNNING);
removeContinuationTriggers();
console.log('[resetProcessingState] Estado resetado.');
}
/** =========================
* Núcleo da contagem
* ========================= */
/**
* Conta arquivos recursivamente a partir da pasta principal.
* Classifica pelo nome da pasta encontrado em qualquer ponto do caminho:
* - Se o caminho contém "Folder X" => bucket correspondente
* - Se não contém nenhuma das 7 => Others
*
* Regras:
* - arquivos diretamente na pasta principal => Others
* - ignora pasta lixeira
* - ignora shortcuts
* - deduplica por file ID
*/
function countFilesRecursivelyByBuckets_(rootFolderId) {
// valida acesso à pasta raiz
getFileMetaWithRetry_(rootFolderId);
const counts = makeEmptyCounts_();
const seenFileIds = Object.create(null);
// fila BFS determinística
const queue = [{
folderId: rootFolderId,
bucket: 'Others'
}];
let scannedFiles = 0;
while (queue.length > 0) {
const current = queue.shift();
const pageIterator = createFolderChildrenIterator_(current.folderId);
while (pageIterator.hasNext()) {
const item = pageIterator.next();
if (!item || !item.id || !item.mimeType) {
continue;
}
// pasta
if (isFolderMimeType_(item.mimeType)) {
const nextBucket = resolveBucketFromFolderName_(item.name, current.bucket);
queue.push({
folderId: item.id,
bucket: nextBucket
});
continue;
}
// ignora shortcut
if (isShortcutMimeType_(item.mimeType)) {
continue;
}
// arquivo real: deduplica por id
if (seenFileIds[item.id]) {
continue;
}
seenFileIds[item.id] = true;
scannedFiles++;
counts[current.bucket] = (counts[current.bucket] || 0) + 1;
}
}
counts.__scanned = scannedFiles;
return counts;
}
/**
* Cria iterador paginado dos filhos imediatos de uma pasta.
* Usa Drive.Files.list com retry.
*/
function createFolderChildrenIterator_(folderId) {
let pageToken = null;
let buffer = [];
let finished = false;
return {
hasNext: function() {
if (buffer.length > 0) {
return true;
}
if (finished) {
return false;
}
const response = listFolderChildrenWithRetry_(folderId, pageToken);
const files = Array.isArray(response.files) ? response.files : [];
// ordena localmente por nome para reduzir variações
files.sort((a, b) => {
const nameA = String(a.name || '').toLowerCase();
const nameB = String(b.name || '').toLowerCase();
return nameA.localeCompare(nameB);
});
buffer = files;
pageToken = response.nextPageToken || null;
if (!pageToken && buffer.length === 0) {
finished = true;
return false;
}
if (!pageToken && buffer.length > 0) {
// ainda há itens no buffer, mas sem próxima página
}
if (!pageToken && buffer.length === 0) {
finished = true;
}
return buffer.length > 0;
},
next: function() {
if (!this.hasNext()) {
throw new Error('Iterator exhausted.');
}
const item = buffer.shift();
if (buffer.length === 0 && !pageToken) {
finished = true;
}
return item;
}
};
}
/**
* Lista os filhos imediatos de uma pasta.
*/
function listFolderChildrenWithRetry_(folderId, pageToken) {
return retryWithBackoff_(() => {
const params = {
q: `'${folderId}' in parents and trashed = false`,
pageSize: CONFIG.PAGE_SIZE,
pageToken: pageToken || undefined,
supportsAllDrives: CONFIG.INCLUDE_SHARED_DRIVES,
includeItemsFromAllDrives: CONFIG.INCLUDE_SHARED_DRIVES,
fields: 'nextPageToken, files(id, name, mimeType, parents)',
corpora: 'allDrives'
};
return Drive.Files.list(params);
}, `listFolderChildren folderId=${folderId} pageToken=${pageToken || ''}`);
}
/**
* Obtém metadados da pasta/arquivo para validar acesso e existência.
*/
function getFileMetaWithRetry_(fileId) {
return retryWithBackoff_(() => {
return Drive.Files.get(fileId, {
supportsAllDrives: CONFIG.INCLUDE_SHARED_DRIVES,
fields: 'id, name, mimeType'
});
}, `getFileMeta fileId=${fileId}`);
}
/** =========================
* Utilitários de classificação
* ========================= */
/**
* Se a pasta atual tiver nome Folder 1..7, passa a valer aquele bucket.
* Senão, herda bucket atual.
*/
function resolveBucketFromFolderName_(folderName, inheritedBucket) {
if (!folderName) {
return inheritedBucket || 'Others';
}
const normalized = normalizeForCompare_(folderName);
for (let i = 1; i <= 7; i++) {
const expected = normalizeForCompare_(`Folder ${i}`);
if (normalized === expected) {
return `Folder ${i}`;
}
}
return inheritedBucket || 'Others';
}
function normalizeForCompare_(value) {
let s = String(value || '').trim();
if (CONFIG.CASE_INSENSITIVE_MATCH) {
s = s.toLowerCase();
}
return s;
}
function isFolderMimeType_(mimeType) {
return mimeType === 'application/vnd.google-apps.folder';
}
function isShortcutMimeType_(mimeType) {
return mimeType === 'application/vnd.google-apps.shortcut';
}
function makeEmptyCounts_() {
return {
'Folder 1': 0,
'Folder 2': 0,
'Folder 3': 0,
'Folder 4': 0,
'Folder 5': 0,
'Folder 6': 0,
'Folder 7': 0,
'Others': 0
};
}
function makeZeroOutputRow_() {
return [[0, 0, 0, 0, 0, 0, 0, 0]][0];
}
/** =========================
* Parsing de links/IDs
* ========================= */
/**
* Resolve folder ID a partir de:
* - ID cru
* - URL comum
* - Rich text link
*/
function resolveFolderIdFromCell_(displayValue, richTextValue) {
// 1) tenta rich text link
const urls = extractUrlsFromRichText_(richTextValue);
for (let i = 0; i < urls.length; i++) {
const idFromUrl = extractDriveIdFromText_(urls[i]);
if (idFromUrl) {
return idFromUrl;
}
}
// 2) tenta pelo texto visível da célula
const idFromDisplay = extractDriveIdFromText_(displayValue);
if (idFromDisplay) {
return idFromDisplay;
}
return '';
}
/**
* Extrai URLs de RichTextValue.
*/
function extractUrlsFromRichText_(richTextValue) {
const urls = [];
if (!richTextValue) {
return urls;
}
try {
const direct = richTextValue.getLinkUrl();
if (direct) {
urls.push(direct);
}
} catch (err) {
// ignora
}
try {
const runs = richTextValue.getRuns();
if (runs && runs.length) {
runs.forEach(run => {
try {
const url = run.getLinkUrl();
if (url) {
urls.push(url);
}
} catch (err) {
// ignora
}
});
}
} catch (err) {
// ignora
}
return urls;
}
/**
* Extrai ID de texto ou URL do Drive.
* Suporta vários formatos comuns.
*/
function extractDriveIdFromText_(text) {
const s = String(text || '').trim();
if (!s) return '';
// casos de URL do Drive
const patterns = [
/\/folders\/([a-zA-Z0-9_-]{10,})/i,
/[?&]id=([a-zA-Z0-9_-]{10,})/i,
/\/d\/([a-zA-Z0-9_-]{10,})/i
];
for (let i = 0; i < patterns.length; i++) {
const match = s.match(patterns[i]);
if (match && match[1]) {
return match[1];
}
}
// caso seja ID cru
if (/^[a-zA-Z0-9_-]{10,}$/.test(s)) {
return s;
}
return '';
}
/** =========================
* Planilha e estado
* ========================= */
function getTargetSheet_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
if (CONFIG.SHEET_NAME) {
const sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
if (!sheet) {
throw new Error(`Aba não encontrada: ${CONFIG.SHEET_NAME}`);
}
return sheet;
}
return ss.getSheets()[0];
}
function scheduleContinuationTrigger_() {
// garante apenas um trigger de continuação pendente
removeContinuationTriggers();
ScriptApp.newTrigger(CONFIG.CONTINUATION_FUNCTION)
.timeBased()
.after(CONFIG.CONTINUATION_TRIGGER_DELAY_MS)
.create();
}
function finishProcessing_() {
const props = PropertiesService.getScriptProperties();
props.deleteProperty(CONFIG.PROP_CURSOR_ROW);
props.setProperty(CONFIG.PROP_IS_RUNNING, 'false');
removeContinuationTriggers();
}
/** =========================
* Retry / robustez
* ========================= */
function retryWithBackoff_(fn, label) {
let lastError = null;
for (let attempt = 1; attempt <= CONFIG.MAX_API_RETRIES; attempt++) {
try {
return fn();
} catch (err) {
lastError = err;
const message = String(err && err.message ? err.message : err);
console.log(`[retryWithBackoff] attempt=${attempt} label=${label} error=${message}`);
// erros fatais que não adianta insistir muito
if (
/File not found/i.test(message) ||
/insufficient permissions/i.test(message) ||
/not found/i.test(message) ||
/invalid/i.test(message)
) {
throw err;
}
if (attempt < CONFIG.MAX_API_RETRIES) {
const waitMs = CONFIG.API_RETRY_BASE_MS * Math.pow(2, attempt - 1);
Utilities.sleep(waitMs);
}
}
}
throw new Error(`Falha após retries em ${label}: ${lastError}`);
}
function truncate_(text, maxLen) {
const s = String(text || '');
return s.length <= maxLen ? s : s.slice(0, maxLen - 3) + '...';
}
Como configurar
- Abra a planilha.
- Vá em Extensões > Apps Script.
- Cole o código.
- Salve o projeto.
- No editor, abra Services e adicione Drive API.
- Execute a função startFullRefresh() uma vez para autorizar.
- Volte para a planilha e recarregue.
- Use o menu Drive Counter.
O uso do serviço avançado do Drive precisa ser habilitado antes do script rodar, e ele usa os mesmos métodos/parâmetros da Drive API pública. Os gatilhos instaláveis são apropriados para automações desse tipo, e PropertiesService é a forma suportada para manter estado simples entre execuções.
Como usar no dia a dia-
Para rodar tudo manualmente:
- execute startFullRefresh()
-
Para continuar um lote interrompido:
- execute processFolderCountsBatch()
-
Para atualização automática de hora em hora:
- execute installHourlyRefresh()
-
Para remover atualização horária:
- execute removeHourlyRefresh()
A regra implementada segue o que foi descrito na demanda: cada linha aponta para uma pasta principal em E; o script entra recursivamente nela; se um arquivo estiver em qualquer caminho que contenha uma subpasta chamada exatamente Folder 1, ele soma em F, e assim por diante até Folder 7; se não passar por nenhuma dessas, cai em Others. Arquivos diretamente na pasta principal também entram em Others. Isso reflete exatamente as confirmações da especificação.
Por que esse script tende a resolver os “zeros” e as contagens instáveisOs sintomas relatados eram: contagem zerando mesmo com arquivos existentes, valores variando entre execuções e dados antigos sem atualização. A solução acima reduz isso porque:
- usa paginação da Drive API em vez de iteradores frágeis;
- ignora atalhos e lixeira explicitamente;
- reconta tudo da linha processada e sobrescreve F:O;
- faz deduplicação por file ID;
- registra erro por linha sem derrubar o lote inteiro;
- separa continuação por lotes com trigger, em vez de tentar fazer tudo numa única execução.
Segurança:
- autorize só a conta que realmente precisa operar as pastas;
- como há Shared Drives, a conta executora precisa ter permissão nelas;
- evite compartilhar o projeto com muitas pessoas sem necessidade.
Performance:
- a escrita em planilha está em lote com setValues();
- o processamento está limitado por BATCH_SIZE;
- a busca no Drive usa paginação.
Manutenção:
- use console.log() para acompanhar cada linha;
- mantenha a configuração centralizada no objeto CONFIG;
- se o volume crescer muito além de 10k+ arquivos com atualização horária, Apps Script pode continuar funcional, mas passa a ficar mais sensível a cotas e tempo de execução. O Google mantém uma página oficial de cotas justamente para esse tipo de monitoramento.
Eu implementei o checkpoint por lote/linha, que é o mais estável dentro do Apps Script para esse cenário. Isso já evita perder o progresso do processamento global. Um checkpoint ultradetalhado “no meio da árvore da pasta” também é possível, mas deixa o script bem mais complexo e aumenta risco de estourar armazenamento/estado no Apps Script. Para a maioria dos casos dessa demanda, checkpoint por linha + gatilho de continuação entrega o melhor equilíbrio entre confiabilidade e manutenção.
Logs e depuraçãoNo editor do Apps Script:
- abra Executions
- clique em uma execução
- veja os console.log()
Você verá mensagens como:
- linha processada
- folder ID resolvido
- total de arquivos escaneados
- contagens finais
- erro da linha, se houver
No bloco CONFIG:
- trocar aba específica em SHEET_NAME
- mudar BATCH_SIZE
- mudar atraso do trigger em CONTINUATION_TRIGGER_DELAY_MS
DimuDesigns
How well does your solution scale? Can it handle 8k - 10k files?
Kildere S Irineu
Sim — 8 mil a 10 mil arquivos é um volume viável, mas com uma observação importante: isso é viável em lote, com continuação por gatilho, e não como uma única execução “instantânea”. O Apps Script tem limites de execução e cotas, então o ponto não é só a quantidade total de arquivos, e sim quantos arquivos/subpastas cada linha precisa varrer e quantas linhas serão reprocessadas na mesma rodada.
Na prática, a solução que eu te passei escala bem para 8k–10k arquivos totais quando esses arquivos estão distribuídos em várias pastas e o processamento roda em batches. Ela usa paginação da Drive API, e o files.list aceita pageSize de até 1000 itens por página, então o script não tenta carregar tudo de uma vez. Isso ajuda bastante na estabilidade.
O ponto de atenção é este: se você tiver uma única linha apontando para uma árvore muito grande, com milhares de arquivos e muitas subpastas, essa linha pode demorar bastante para terminar dentro de uma execução do Apps Script. Nesse cenário, o gargalo não é a lógica do script em si, mas o tempo disponível por execução e as cotas do serviço.
Então minha resposta honesta é:
- 8k–10k arquivos: sim, a arquitetura suporta.
- Atualização completa recorrente de tudo: também suporta, desde que você aceite que isso rode em múltiplos lotes por trigger.
- Tempo real ou quase instantâneo para volumes altos: aí já não é o melhor cenário para Apps Script puro.
Para esse tamanho, eu classificaria assim:
Até ~10 mil arquivos totais
A solução atual deve funcionar bem na maioria dos casos, principalmente se:
- o lote continuar em trigger,
- a conta tiver acesso correto às pastas,
- e o número de linhas processadas por execução for controlado.
Acima disso, ou com reprocessamento muito frequente
Eu melhoraria a arquitetura para não revarrer tudo sempre. O caminho mais eficiente seria usar a Drive Changes API para detectar só o que mudou e recalcular apenas as pastas afetadas, em vez de fazer full scan a cada atualização. A API de mudanças existe justamente para listar alterações em vez de reler toda a estrutura.
Resumindo: sim, 8k–10k arquivos é um alvo realista para a solução proposta, mas eu não venderia isso como “escalabilidade infinita”. Ela é boa para esse porte com batches e triggers; para volumes maiores ou atualizações muito frequentes, eu recomendaria uma versão com cache + checkpoint mais granular + Drive Changes API.
Se o objetivo for escalar isso com segurança e eficiência, posso atuar na evolução da arquitetura e implementação de uma solução mais performática e sustentável.
--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/SvFBGQRMaxE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/a53e89d0-d189-494a-a1e7-a217a1f6049fn%40googlegroups.com.
DimuDesigns
Even with batch processing, I strongly suspect your approach will exhaust the 90-minute per day trigger runtime quota for personal accounts.
At OPs target volume (8k to 10k files), your script will likely execute several time-based continuation triggers in sequence per run, in addition to the hourly refresh trigger. I'm thinking that would burn through the service quota fairly quickly.
Real-time or near real-time updates are certainly out of the question with this strategy.
You've touched on a possible alternative using the Drive Changes API - but doesn't that require setting up a watch on a Google Drive file or folder, and deploying a webhook endpoint to receive push notifications? Moreover, Drive API push notifications send their payloads primarily as HTTP headers in a POST request, and we cannot access headers from a GAS Web App's doPost(e) trigger (see issue tracker topic).
Given the file volume and real-time update requirements GAS is probably not the right tool for the job.
Kildere S Irineu
You raise very valid concerns, and I agree with most of your assessment — especially regarding quotas and real-time constraints in Apps Script.
From a technical standpoint, the proposed approach (full recursive scan + batch continuation triggers) is viable for moderate-scale batch processing, but it does have clear limitations when applied to higher volumes or frequent refresh cycles.
1. Runtime quota considerations
For personal accounts, the 90-minute/day trigger runtime quota is indeed the primary bottleneck. Even with batching, a dataset in the range of 8k–10k files can require multiple chained executions to complete a full refresh, particularly when:
- folder structures are deeply nested,
- there are many subfolders to traverse,
- or multiple rows need to be processed.
In such cases, repeated continuation triggers + scheduled refreshes can realistically exhaust the daily quota. So your concern here is absolutely justified.
2. Suitability of the current approach
The architecture I proposed is best suited for:
- periodic batch updates (e.g., hourly or daily),
- eventual consistency, rather than immediate accuracy,
- environments where occasional delays in synchronization are acceptable.
It is not designed for near real-time updates, and I would not position Apps Script as an ideal solution for that requirement.
3. About Drive Changes API
One clarification: the Drive Changes API does not strictly require push notifications.
It can be used in a polling model, leveraging:
-
changes.getStartPageToken -
changes.list
This allows tracking incremental changes since the last execution, significantly reducing the need for full rescans. This alone can drastically improve scalability within Apps Script constraints.
However, you are correct that:
- push notifications (watch + webhook) introduce additional complexity,
- and Apps Script Web Apps are not well-suited for handling them robustly.
In particular, Drive push notifications rely heavily on HTTP headers, and Apps Script’s doPost(e) interface does not provide full access/control over request headers, which limits its usability as a webhook receiver in this context.
4. Architectural conclusion
So I’d summarize it this way:
- Yes, the batch + trigger approach works for the stated volume under controlled conditions.
- Yes, it can hit quota limits depending on usage patterns.
- No, it is not appropriate for real-time or high-frequency update requirements.
For higher scale or responsiveness, a more suitable architecture would be:
- incremental processing via Drive Changes API (polling), or
- a backend outside Apps Script (e.g., Cloud Run / Functions) handling push notifications or event-driven processing.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/c985aaca-3aaf-47b7-902d-db7b2eba6898n%40googlegroups.com.
James
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ-AUmiVObOt1h7skwQFvWJRmmwyV%3DJRaffBtUc58-o6s0K1cQ%40mail.gmail.com.