Google Apps Script-DriveApp and SpreadsheetApp tutorial

In this series of tutorials, we will use Google Apps Script to automate some of the work related to G Suite Applications. If you use Google Drive, Google Sheets or G Suite applications extensively, then using Google Apps Script might help automating lot of your work.

Google Apps Script is an application development platform which is integrated with all your Google applications like Google drive, docs, sheet, slides etc. You can write the code in Javascript and it has in-build libraries to access all your google applications.

Recommended books/courses on Google Apps Script

Google Apps Script Complete Course New IDE 100+ Examples

Problem Statement

In this tutorial, we will write a script to count all files inside all subfolders of a Google drive folder and write the counts in a google sheet. This can be helpful in a number of ways. Google drive does not show count of all files. One trick you can do is select all files and try to drag the selection, it will show you the count. But, if you are in folder with few hundred files, it takes time to load. You have to scroll down to the end of the list for google drive to load all files. Also, if number of files in the folder is changing on regular basis, manual count is not efficient. so always follow DRY principle 🙂 Do Not Repeat yourself. If you have to repeat something, you can automate.

In above image, you can see TopFolder has four Images folder, each of them has files with extension jpg Please note down the folder id from the URL which we will use in our script later on. (Shown in red box above).

Also, I have created a google sheet with name “Stats”, you can use any existing sheet.

Open this file and add new sheet with name “Counts” which should be empty. We will update our counts in this sheet.

Apps Script – How to open the Script Editor

Now, in Tools menu, click “Script Editor”, it will open new google apps script editor with code.gs file and one empty function. Save the project with some name. Let’s add one function with name countFiles. Complete code is available at the end of this article.

First Apps Script function

Add following code in countFiles function.

var TOP_FOLDER_ID = "<GET_IT_FROM_FOLDER_URL>";

function countFiles() {
  Logger.log("countFiles called");
  var topFolder = DriveApp.getFolderById(TOP_FOLDER_ID);
  var subFolders = topFolder.getFolders();
  while(subFolders.hasNext()) {
    var subFolder = subFolders.next();
    var subFolderName = subFolder.getName();
    Logger.log(subFolderName);  }
};

In this function, we are using DriveApp Class from Drive service provided by Google Apps Scripts. For TOP_FOLDER_ID variable use the value of folder id available in URL for TopFolder (Check the first image).

getFolderById function give you the access to Folder Object which allows you to iterate over subfolders.

As you can see below, editor also provides list of all available functions for known objects.
It has many more functions to create, find and modify files in Drive. We have iterated here over each sub folder in TopFolder and logged its name.

Authorization for running Apps Script

Each function we add comes up in function dropdown list in the Toolbar. You can select any function there and run it. As shown in red box below, Select countFiles function and run it.

When you run it first time, it will ask for authorization. Whenever you use a new service through the script first time, you will need to give permission. e.g. you are allowing this project to access your files in Drive.

After it starts running without any error, select Logs from View menu. You should see logs like shown below in the output.

Counting Files function and Output

Now let’s count files inside each subfolder. Following simple function does the job. It takes folder object as input and iterate over all files and increase the fileCount.

var SEARCH_FILE_EXT = "jpg";

function countFilesInFolder(folder) {
  var fileCount = 0;
  var fileList = folder.getFiles();
  while(fileList.hasNext()) {
    var file = fileList.next();
    var fileName = file.getName();
    //Logger.log(fileName);
    if(fileName.indexOf(SEARCH_FILE_EXT)>=0) {
      fileCount++;
    }
  }
  return fileCount;
};

Call this function from countFiles() function for each subFolder like this.

var TOP_FOLDER_ID = "16xvTf9BTOO7M2QdVEdhINUsy588u6zs_";
function countFiles() {
  Logger.log("countFiles called");
  var topFolder = DriveApp.getFolderById(TOP_FOLDER_ID);
  var subFolders = topFolder.getFolders();
  while(subFolders.hasNext()) {
    var subFolder = subFolders.next();
    var subFolderName = subFolder.getName();
    Logger.log(subFolderName);
    var count = countFilesInFolder(subFolder);
    Logger.log("File Count:"+count);
  }
};

It will have logs output like this.

Storing output in Google Sheets

Now let’s write a function to store these counts in our spreadsheet.

Following function is using SpreadsheetApp class of Spreadsheet Service, it gets the active spreadsheet and find the sheet by name: “Counts”. First we are going to write the header (first row). First column for “Folder Name” and second column “Total Files”.

getRange(row, column) function takes row number and column number and returns you access to Cell. Finally setValue() is used to write the value in cell.
Keep in mind that row number starts from 1 and not 0.

var COUNTS_SHEET = "Counts";
var COUNTS_SHEET_FOLDER_NAME_COLUMN = 1;
var COUNTS_SHEET_FILE_COUNT_COLUMN = 2;

function writeHeadersInSheet(){
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(COUNTS_SHEET);
    var cell = sheet.getRange(1,COUNTS_SHEET_FOLDER_NAME_COLUMN);
    cell.setValue("Folder Name");
    cell = sheet.getRange(1,COUNTS_SHEET_FILE_COUNT_COLUMN);
    cell.setValue("Total Files");
  } catch (e) {
    Logger.log(e.toString());
  }
};

Following function does the similar job as before, but it takes rowNo as input, folderName and fileCount to write in respective cells.

function writeCountInSheet(rowNo,folderName, fileCount){
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(COUNTS_SHEET);
    var cell = sheet.getRange(rowNo,COUNTS_SHEET_FOLDER_NAME_COLUMN);
    cell.setValue(folderName);
    cell = sheet.getRange(rowNo,COUNTS_SHEET_FILE_COUNT_COLUMN);
    cell.setValue(fileCount);
  } catch (e) {
    Logger.log(e.toString());
  }
};

Let’s use above function in our countFiles function as shown below.

function countFiles() {
  Logger.log("countFiles called");
  writeHeadersInSheet();
  var topFolder = DriveApp.getFolderById(TOP_FOLDER_ID);
  var subFolders = topFolder.getFolders();
  var rowNo = 2;
  while(subFolders.hasNext()) {
    var subFolder = subFolders.next();
    var subFolderName = subFolder.getName();
    Logger.log(subFolderName);
    var count = countFilesInFolder(subFolder);
    Logger.log("File Count:"+count);
    writeCountInSheet(rowNo,subFolderName,count);
    rowNo++;
  }
};

Now our function is complete and it does the job of counting and storing in google sheet.

Bonus- Add functions as Menu Items

Let’s add our function calls as menu item in our spreadsheet so we can call it without opening our Script editor every time.
First below new function clear all values from our sheet.

function resetSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(COUNTS_SHEET);
  var range = sheet.getRange(1,COUNTS_SHEET_FOLDER_NAME_COLUMN,sheet.getLastRow(),2);
  range.clear();
}

Below function onOpen is called every time when our google sheet is opened. It will add following Menu items.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("File Stats",
             [
               { name: "Get Counts", functionName: "countFiles" },
               { name: "Clear Counts", functionName: "resetSheet" },
             ]
            );
}

After saving the script, reload the google sheet. As you can see, new menu with two items has been added. Click “Clear Counts” to clear the sheet and “Get Counts” to count files again.

With automation like above, you can save lot of time. Just think about anything which you have to repeat in your daily operations with G Suite and try to do the same with Google Apps Script. I have used it before to reconciliation of bank statements with transactions provided by multiple different people. Also, one of the most useful examples is statistics, when filters in Google sheet are not enough and you need to do many different ways of counting certain data from google sheet, you can use Apps Script. Read the sheet, add logic for counts and update in a new sheet. Sales or expense data over months/quarters can be done with very basic functions. There is a way to publish these data programmatically in a web page as charts or table also.

Hope the above tutorial is helpful to you. Please leave your feedback/comments below and let me know if anything specific example you are looking for.

Complete Code

var TOP_FOLDER_ID = "16xvTf9BTOO7M2QdVEdhINUsy588u6zs_";
var SEARCH_FILE_EXT = "jpg";
var COUNTS_SHEET = "Counts";
var COUNTS_SHEET_FOLDER_NAME_COLUMN = 1;
var COUNTS_SHEET_FILE_COUNT_COLUMN = 2;

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("File Stats",
             [
               { name: "Get Counts", functionName: "countFiles" },
               { name: "Clear Counts", functionName: "resetSheet" },
             ]
            );
}

function resetSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(COUNTS_SHEET);
  var range = sheet.getRange(1,COUNTS_SHEET_FOLDER_NAME_COLUMN,sheet.getLastRow(),2);
  range.clear();
}

function writeHeadersInSheet(){
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(COUNTS_SHEET);
    var cell = sheet.getRange(1,COUNTS_SHEET_FOLDER_NAME_COLUMN);
    cell.setValue("Folder Name");
    cell = sheet.getRange(1,COUNTS_SHEET_FILE_COUNT_COLUMN);
    cell.setValue("Total Files");
  } catch (e) {
    Logger.log(e.toString());
  }
};

function writeCountInSheet(rowNo,folderName, fileCount){
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(COUNTS_SHEET);
    var cell = sheet.getRange(rowNo,COUNTS_SHEET_FOLDER_NAME_COLUMN);
    cell.setValue(folderName);
    cell = sheet.getRange(rowNo,COUNTS_SHEET_FILE_COUNT_COLUMN);
    cell.setValue(fileCount);
  } catch (e) {
    Logger.log(e.toString());
  }
};

function countFiles() {
  Logger.log("countFiles called");
  writeHeadersInSheet();
  var topFolder = DriveApp.getFolderById(TOP_FOLDER_ID);
  var subFolders = topFolder.getFolders();
  var rowNo = 2;
  while(subFolders.hasNext()) {
    var subFolder = subFolders.next();
    var subFolderName = subFolder.getName();
    Logger.log(subFolderName);
    var count = countFilesInFolder(subFolder);
    Logger.log("File Count:"+count);
    writeCountInSheet(rowNo,subFolderName,count);
    rowNo++;
  }
};

function countFilesInFolder(folder) {
  var fileCount = 0;
  var fileList = folder.getFiles();
  while(fileList.hasNext()) {
    var file = fileList.next();
    var fileName = file.getName();
    //Logger.log(fileName);
    if(fileName.indexOf(SEARCH_FILE_EXT)>=0) {
      fileCount++;
    }
  }
  return fileCount;
};