Google Drive

Analyzing Folder Structures with Google Apps Script

Gists

Abstract

This report provides a Google Apps Script to retrieve all files, including those within subfolders, for a designated folder. It addresses the challenges of retrieving files within deeply nested folder structures and obtaining complete file paths.

Introduction

Google Apps Script empowers developers to interact with Google Drive data programmatically, unlocking a wide range of functionalities. A core component of this interaction is the Drive service (DriveApp) and Drive API. These services provide programmatic control over files and folders within your Drive.

Updated: GAS Library - MoveFolder

MoveFolder was updated to v1.0.1.

  • v1.0.1 (June 18, 2024)

    1. In the recent update on the Google side, it was found that in the current stage, when the other libraries are loaded from a library, an error like We're sorry, a server error occurred while reading from storage. Error code NOT_FOUND occurs. So, from v1.0.1, the library of BatchRequest is included in this library.

You can see the detail information here https://github.com/tanaikech/MoveFolder

GAS Library - MoveFolder

Overview

This is a Google Apps Script library for moving a folder including files and folders on Google Drive.

Description

This library addresses a common challenge: efficiently moving folders, including their subfolders and files, between Google Drives. This encompasses both personal and shared drives using a script. While Google Drive offers straightforward methods for moving individual files between any drives, directly moving entire folders containing subfolders presents limitations, particularly when shared drives are involved. This script bridges that gap by providing a reliable Google Apps Script solution for such scenarios.

Retrieving Specific Folders from Google Drive using Google Apps Script

Gists

These are sample scripts for retrieving specific folders from Google Drive using Google Drive service (DriveApp) with Google Apps Script.

Retrieving folders in own Google Drive

const folders = DriveApp.searchFolders(
  `'${Session.getActiveUser().getEmail()}' in owners and trashed=false`
);
const res = [];
while (folders.hasNext()) {
  const folder = folders.next();
  res.push(folder.getName());
}
console.log(res);

Retrieving folders in shared Drives

const folders = DriveApp.searchFolders(
  `not '${Session.getActiveUser().getEmail()}' in owners and trashed=false`
);
const res = [];
while (folders.hasNext()) {
  const folder = folders.next();
  const owner = folder.getOwner();
  if (owner === null) {
    res.push(folder.getName());
  }
}
console.log(res);

Retrieving folders of sharedWithMe

const folders = DriveApp.searchFolders(`sharedWithMe`);
const res = [];
while (folders.hasNext()) {
  const folder = folders.next();
  res.push(folder.getName());
}
console.log(res);

Retrieving Files of 'Shared with Me' in Specific Folder using Google Apps Script

Gists

This is a sample script for retrieving the files of ‘Shared with Me’ in the specific folder using Google Apps Script.

In the current stage, when you transfer the ownership of your file on your Google Drive to another user and/or copy the file “Shared with me” to the specific folder on your Google Drive, the file becomes the shortcut file. Under this situation, when you want to retrieve the files of “Shared with me” in the specific folder, unfortunately, the following search query cannot be used.

Retrieving Files and Folders without Parents in Google Drive

Gists

This is a sample script for retrieving the files and folders which have no parents in own Google Drive.

When you use this script, please enable Drive API at Advanced Google services.

Sample script

const myFunction = () => {
  const token = ScriptApp.getOAuthToken();
  const fields = decodeURIComponent(
    "nextPageToken,files(name,id,mimeType,parents)"
  );
  const q = decodeURIComponent("'me' in owners and trashed = false");
  let files = [];
  let pageToken = "";
  do {
    const res = UrlFetchApp.fetch(
      `https://www.googleapis.com/drive/v3/files?pageSize=1000&fields=${fields}&q=${q}&pageToken=${pageToken}`,
      { headers: { authorization: `Bearer ${token}` } }
    );
    const obj = JSON.parse(res);
    Array.prototype.push.apply(files, obj.files);
    pageToken = obj.nextPageToken;
  } while (pageToken);
  const result = files.filter(({ parents }) => !parents);
  console.log(result);
};

When you run the script, the files and folders which have no parents in own Google Drive are retrieved.

Moving File to Specific Folder using Google Apps Script

Gists

These are 3 sample scripts for moving a file to the specific folder in Google Drive using Google Apps Script.

Sample script 1

In this script, only Drive Service is used.

var sourceFileId = "###";
var destinationFolderId = "###";

var file = DriveApp.getFileById(sourceFileId);
DriveApp.getFolderById(destinationFolderId).addFile(file);
file
  .getParents()
  .next()
  .removeFile(file);

Sample script 2

In this script, only Drive API at Advanced Google services. (In this case, it’s Drive API v2.)

var sourceFileId = "###";
var destinationFolderId = "###";

Drive.Files.update({ parents: [{ id: destinationFolderId }] }, sourceFileId);

Sample script 3

In this script, only Drive API v3 is used.

One Time Download for Google Drive

Overview

This is a sample script for downloading files from Google Drive by the one time download method.

Description

When you download a file from Google Drive, in generally, the login and the access token are required. If you want to download the file without the authorization for the simple situation, the file is required to be publicly shared. But the file might not be able to be shared publicly, because of various reasons.

Uploading File to Shared Folder using ggsrun

Gists

ggsrun is also a CLI application for using Google Drive.

Here, I would like to introduce a sample command. This is a sample command for uploading a file to a shared folder using ggsrun.

This situation supposes that the shared folder is https://drive.google.com/drive/folders/abcdefg?usp=sharing and the folder has the edit permission.

Sample command:

$ ggsrun u -f "sample.txt" -p "abcdefg" --serviceaccount "###JSON file of Service Account###"
  • If you have already used OAuth2, you can upload the file by ggsrun u -f "sample.txt" -p "###folderId###".

(NEW) Retrieve old revision file from Google Drive

This method was updated at July 12, 2017.

In order to use this, at first, please retrieve your access token and enable Drive API.

1. File ID

Retrieve file id from file name.

curl -X GET -sSL \
    -H 'Authorization: Bearer ### Access token ###' \
    'https://www.googleapis.com/drive/v3/files?q=name="### FileName ###"&fields=files(id,name)'

Reference : https://developers.google.com/drive/v3/reference/files/list

2. Revision ID

Retrieve revision id from file id.

curl -X GET -sSL \
    -H 'Authorization: Bearer ### Access token ###' \
    'https://www.googleapis.com/drive/v3/files/### FileID ###/revisions?fields=revisions(id%2CmodifiedTime)'

Reference : https://developers.google.com/drive/v3/reference/revisions/list

Retrieving HTML File ID from Microsoft Docx File on Google Drive

This sample script converts from Microsoft Docx File on Google Drive to Google Spreadsheet, and converts to HTML file.

Drive APIs v2 and v3 are used for this. Please set as follows.

“Drive API v2” can be used at Google Apps Script by enabling Drive API of Advanced Google services and of Google API Console.

How to use it is as follows.

  1. In the script editor, select Resources > Advanced Google services

Creating Spreadsheet from Excel file

These scripts can be executed on Script Editor. But, in order to use these, you have to enable Drive API of Advanced Google services and of Google API Console. “Drive API v2” can be used at Google Apps Script by enabling Drive API of Advanced Google services and of Google API Console.

How to use it is as follows.

  1. In the script editor, select Resources > Advanced Google services

  2. In the dialog that appears, click the on/off switch for Drive API v2.

Creating Downloaded Excel file as Spreadsheet

This is a sample GAS script to create an Excel file, which was downloaded from web, as Spreadsheet. By using Drive API, it can be achieved without access token.

Script :

function downloadFile(fileURL, folder) {
  var filename = fileURL.match(".+/(.+?)([\?#;].*)?$")[1];
  var response = UrlFetchApp.fetch(fileURL);
  var rc = response.getResponseCode();
  var blob = response.getBlob();
  var resource = {
    "mimeType": "application/vnd.google-apps.spreadsheet",
    "parents": [{id: folder}],
    "title": filename
  };
  var res = Drive.Files.insert(resource, blob);
  var fileInfo = [rc, res.title, blob.getBytes().length, res.id];
  return fileInfo;
}

Result :

[
    200,
    sample.xlsx,
    10000.0,
    ## file id ##
]

How to use "fields" of Drive APIs

There are a lot of APIs on Google. When we use Google Drive APIs, they usually have “fields” as a resource. The parameter “fields” gives various information which is selected to us. This is one of important parameters. And this can be used at Google Apps Script (GAS) although that version is v2. About how to use it, there are some documents. But it is difficult to find how to use it at GAS. So I would like to write down here as a memorandum. Most parameters for APIs have to be expressed as JSON. However, the expressions are different for each API. I would like to introduce this using some samples. This is for GAS.

Retrieve old revision file from Google Drive

I introduce 2 kinds of methods. One is to use curl. Another is to use wget. At this time, I could know that wget can be also used as same as curl.

In order to use this, at first, please retrieve your access token and enable Drive API.

1. File ID

Retrieve file id from file name.

curl -X GET -sSL \
    -H 'Authorization: Bearer ### Access token ###' \
    'https://www.googleapis.com/drive/v3/files?q=name="### FileName ###"&fields=files(id,name)'

wget -q --header='Authorization: Bearer ### Access token ###' \
    'https://www.googleapis.com/drive/v3/files?q=name="### FileName ###"&fields=files(id,name)' \

Reference : https://developers.google.com/drive/v3/reference/files/list