Drive Force Sync

drive-force-sync.gs

// Copyright 2024 Rob Brown

// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at

//     http://www.apache.org/licenses/LICENSE-2.0

// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

// Drive Force Sync
// Created by Rob Brown 2024-03-23
// 
// Script to "force sync" Synology NAS Cloud Sync for Google Drive - see https://www.reddit.com/r/synology/comments/1bh6uv3/cloud_sync_google_drive_moved_folder/ for my original post.
// 
// Folders moved on Drive from outside of my configured "Remote path" in Cloud Sync into the remote path would sometimes not sync on the NAS until the files were
// opened in the Drive web app. If you viewed the synced folder on the NAS it was like the folders and files never existed.
// 
// So the directory structure would exist on Drive but not my NAS. This script fixes that.
// 
// This script iterates through the specified folder on Drive
// and then adds a description to "tickle" each file into syncing. The last modified date is then reset to the date/time BEFORE this tool ran.
// 
// State is stored in a Google Sheets Spreadsheet so in the case of timeout, the script can simply be run again and it will resume where it left off.
// The idea is to run this script once for a folder that isn't present on the NAS, and everything should stay in sync thereafter.
//
// To install go to https://www.google.com/script/start/ click "Start Scripting" and copy and paste this script into a new project.
// 
// Click "Project Settings" on the left menu and then set the value of
// "folderId" and "email" under "Script Properties" by adding new properties.
// 
// folderId:-
// Supply the ID of the folder you wish to sync, or even your root folder.
//
// To find the ID, double click the folder in the Drive web app then copy the ID from the path, which is in the following format:
// https://drive.google.com/drive/folders/ID_HERE
// 
// Not tested, but the literal "root" can be used for "sync all": https://developers.google.com/drive/api/guides/folder
// 
// email:-
// The email address to notify when syncing is completed. This might take several hours depending on the folder size, so it is useful to get a notification when done.
// 
// Choose "main" from the dropdown in the editor and click Run to test it and it will start syncing.
//
// If the script seems happy on manual run, either wait for first execution to timeout, or feel free to stop it and setup a trigger to complete your task
// (Triggers is on the left-hand menu in Google Apps Scripts).
//
// It is recommended to schedule the main function, but don't schedule it to run more often than the timeout period for Google Scripts 
// as it is not coded for concurrency (every 6 minutes at the time of writing, but please check https://developers.google.com/apps-script/guides/services/quotas). At the time of writing,
// "every 10 minutes" is recommended.
//
// The script will automatically pickup from where it left off upon resuming, and may need to run itself several times to complete your sync.
//
// # FAQ 
// ## Error message: "The uncompleted folder is not a child of the one you wanted completing!"
//
// You might get this if you change which folders you are updating - in this case delete the uncompleted rows from the spreadsheet, "Progress Tracker" tab
//
// ## Error message: "Already completed.""
// 
// This means as far as this script is concerned, it has already completed syncing this folder. // If you want to run it again, follow these instructions:
// 
// In column "B" of the Spreadsheet (which you can find at https://docs.google.com/spreadsheets/u/0/) add the word "TRUE" to the "ALL COMPLETE" "Log" entry for the
// folder so the previous completion is ignored, then run again.

// Customise to your liking - these refer to the spreadsheet names created in Google Sheets for saving the status then resuming
const STATUS_SHEET_NAME = "Progress Tracker";
const SPREADSHEET_TITLE = "Folder Processing Tracker";
const LOGS_SHEET_NAME = "Log";

// If you want to sync all files again, e.g. to a different NAS or Cloud Sync target, change both SPECIAL_VALUE and SEARCH_VALUE. e.g. 0001 to 0002
const SPECIAL_VALUE = JSON.stringify({rbSync0001: getCurrentUTCDateTime(), scriptUrl: "https://gist.github.com/rbrown256/f17dfac17fc8c32e595058866909b44f" });
const SEARCH_VALUE = "rbSync0001";

// No need to set, this is stored in userProperties after creation
var SPREADSHEET_ID = ""; 

// No need to touch anything past here
var START_MARKER;
var COMPLETE_MARKER;
var email;

var spreadsheet;
var statusSheet;
var folderData;
var continuationSheet;
var logSheet;

// Debug function to check the dirtory tree on Drive. Just set initial function to use `tree` instead of `main`
function tree(folderId, indent, fileCount, folderCount, totalCount) {

  var topLevel = false;

  if (!indent) {
    indent = 1;
    topLevel = true;

    fileCount = 0;
    folderCount = 0;
    totalCount = 0;
  }

  if (!folderId) {
    var scriptProperties = PropertiesService.getScriptProperties();

    folderId = scriptProperties.getProperty("folderId");
    Logger.log(`Folder ID picked up is ${folderId}`);
  }

  var indentString = '';

  for (var i = 0; i <= indent; i++) {
    indentString += ' ';
  }

  var folder = DriveApp.getFolderById(folderId);

  if (topLevel) {
    console.log(folder.getName());
  }

  var files = folder.getFiles();

  while (files.hasNext()) {
    var file = files.next();

    var fileName = file.getName();

    if (!fileName.endsWith(".gdoc") && !fileName.endsWith(".gsheet")) {
      fileCount += 1;
      totalCount += 1;
    }

    console.log(indentString, fileName);
  }

  var folders = folder.getFolders();

  while (folders.hasNext()) {
    var subFolder = folders.next();

    console.log(indentString, subFolder.getName());

    folderCount += 1;
    totalCount += 1;

    var subValues = tree(subFolder.getId(), indent + 1, fileCount, folderCount, totalCount);

    fileCount = subValues.fileCount;
    folderCount = subValues.folderCount;
    totalCount = subValues.totalCount;

    console.log(indentString, "---------------------------------");
    console.log(indentString, "Files", fileCount);
    console.log(indentString, "Folders", folderCount);
    console.log(indentString, "Total", totalCount);
    console.log(indentString, "---------------------------------");
  }

  var returnObject = new Object();

  returnObject.fileCount = fileCount;
  returnObject.folderCount = folderCount;
  returnObject.totalCount = totalCount;

  return returnObject;
}

function main() {
  var userProperties = PropertiesService.getUserProperties();

  SPREADSHEET_ID = userProperties.getProperty("SPREADSHEET_ID");

  var scriptProperties = PropertiesService.getScriptProperties();

  var folderId = scriptProperties.getProperty("folderId");
  Logger.log(`Folder ID picked up is ${folderId}`);

  email = scriptProperties.getProperty("email");
  Logger.log(`Email picked up is ${email}`);

  START_MARKER = `************ START MARKER |${folderId}|`;
  COMPLETE_MARKER = `************ ALL COMPLETE |${folderId}|`;
  
  Logger.log(`Spreadsheet ID saved was ${SPREADSHEET_ID}`);

  try {
    spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  }
  catch {
    Logger.log("Could not open spreadsheet");
  }

  if (spreadsheet) {
    var spreadsheetId = spreadsheet.getId();
    var file = DriveApp.getFileById(spreadsheetId);

    if (file.isTrashed()) {
      Logger.log("Spreadsheet was trashed");
      spreadsheet = null;
    } else {
      Logger.log(`Found spreadsheet ID ${spreadsheetId}`);
      continuationSheet = spreadsheet.getSheetByName("c-tokens");
    }
  }

  if (!spreadsheet) {
    Logger.log("Creating new spreadsheet");
    spreadsheet = createSpreadsheet();
  }

  userProperties.setProperty("SPREADSHEET_ID", spreadsheet.getId());

  statusSheet = spreadsheet.getSheetByName(STATUS_SHEET_NAME) || spreadsheet.insertSheet(STATUS_SHEET_NAME);
  logSheet = spreadsheet.getSheetByName(LOGS_SHEET_NAME);
  
  if (!logSheet) {
    logSheet = spreadsheet.insertSheet(LOGS_SHEET_NAME);

    logSheet.appendRow(["dateTime", "ignore", "logEntry"])
  }

  folderData = statusSheet.getDataRange().getValues().slice(1);

  if (checkLogsForPreviousCompletion()) {
    return false;
  }

  var folder = DriveApp.getFolderById(folderId);

  var firstRunForFolder = !checkLogsForStartMarker();

  if (firstRunForFolder) {
    writeLog(START_MARKER);
    createFolderEntry(folder);
  }

  var doLoop = true;
  while (doLoop) {
    doLoop = updateMetadataForAllFiles(folderId);
  }
}

function createSpreadsheet() {
  const spreadsheet = SpreadsheetApp.create(SPREADSHEET_TITLE);
  const sheet = spreadsheet.getActiveSheet().setName(STATUS_SHEET_NAME);

  // Create header row
  sheet.appendRow(["folderId", "url", "path", "recordCreated", "folderEnumerated", "dateCompleted"]); 

  SPREADSHEET_ID = spreadsheet.getId();
  Logger.log("Created SPREADSHEET_ID " + SPREADSHEET_ID);

  var infoSheet = spreadsheet.getSheetByName("README") || spreadsheet.insertSheet("README");
  infoSheet.appendRow(["Used to maintain status for Drive Force Sync - https://gist.github.com/rbrown256/f17dfac17fc8c32e595058866909b44f. Safe to delete if you do not use this tool."])

  continuationSheet = spreadsheet.getSheetByName("c-tokens") || spreadsheet.insertSheet("c-tokens");
  continuationSheet.appendRow(["folderId", "url", "path", "isFolders", "recordCreated", "token", "completed"]);

  return spreadsheet; 
}

function getCurrentUTCDateTime() {
  var now = new Date();
  var utcDateTime = Utilities.formatDate(now, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
  return utcDateTime;
}

function writeLog(entry) {
  Logger.log(entry);
  logSheet.appendRow([getCurrentUTCDateTime(), "", entry]);
}

function getAllParents(folder) {
  var parents = folder.getParents();

  var allParents = [];

  while (parents.hasNext()) {
    allParents[allParents.length] = parents.next();
  }

  var uniqueParents = [];

  for (var i = 0; i < allParents.length; i++) {
    var parent = allParents[i];
    if (!uniqueParents.includes(parent)) {
      uniqueParents.push(parent);
      uniqueParents = uniqueParents.concat(getAllParents(parent));  
    }
  }

  return uniqueParents;
}

// Put something inside the 'ignore' column to run the main folder again
function checkLogsForPreviousCompletion() {
  var logData = logSheet.getDataRange().getValues();

  for (var i = 0; i < logData.length; i++) {
    if (logData[i][2] === COMPLETE_MARKER && logData[i][1] === "") {
      writeLog("Already completed.");
      return true;
    }
  }
  return false;
}

function checkLogsForStartMarker() {
  var logData = logSheet.getDataRange().getValues();

  for (var i = 0; i < logData.length; i++) {
    if (logData[i][2] === START_MARKER && logData[i][1] === "") {
      return true;
    }
  }
  return false;
}

function setIgnoreStartMarker() {
  var logData = logSheet.getDataRange().getValues();

  for (var i = 0; i < logData.length; i++) {
    if (logData[i][2] === START_MARKER && logData[i][1] === "") {
      logData[i][1] = true;
      logSheet.getRange(i + 1, 1, 1, logData[i].length).setValues([logData[i]]); 
      return;
    }
  }
}

function updateMetadataForAllFiles(folderId) {

  writeLog(`Starting execution for folder ID ${folderId}`);

  var folder = DriveApp.getFolderById(folderId);
  
  var uncompleted = findUncompletedFolders();

  writeLog("Number of uncompleted folders: " + uncompleted.length);

  if (uncompleted.length === 0) {
    MailApp.sendEmail(email, "Drive Force Sync Complete " + folderId + " " + getFolderName(folder) + "/" + folder.getName(), getCurrentUTCDateTime());
    writeLog(COMPLETE_MARKER);
    setIgnoreStartMarker();
    return false;
  }

  for (var i = 0; i < uncompleted.length; i++) {
    var currentFolder = DriveApp.getFolderById(uncompleted[i]);

    if (folderId !== uncompleted[i]) {

      var parents = getAllParents(currentFolder);

      var result = parents.find(e => e.getId() === folderId);

      if (!result) {
        writeLog("The uncompleted folder is not a child of the one you wanted completing! " + uncompleted[i]);
        return false;
      }
    }
  
    writeLog("Completing folder: " + uncompleted[i] + " " + getFolderName(currentFolder) + "/" + currentFolder.getName());
    processFolder(currentFolder, false);
    
  }
  writeLog("Running again to ensure everything completed");

  return true;
}

function findUncompletedFolders() {
  
  var data = statusSheet.getDataRange().getValues();
  var ids = [];

  for (var i = 0; i < data.length; i++) {
    if (data[i][5] === "") { // No completion date
      ids.push(data[i][0]); // Store the ID
    }
  }

  return ids;
}

function markFolderDone(id) {
  
  var data = statusSheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    if (data[i][0] == id && data[i][5] === "") { 
      data[i][5] = getCurrentUTCDateTime();
      statusSheet.getRange(i + 1, 1, 1, data[i].length).setValues([data[i]]); 
      return;
    }
  }

  writeLog("markFolderDone: ID not found: " + id); 

}

function markFolderEnumerated(id) {
  
  var data = statusSheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    if (data[i][0] == id && data[i][4] === "") { 
      data[i][4] = getCurrentUTCDateTime();
      statusSheet.getRange(i + 1, 1, 1, data[i].length).setValues([data[i]]); 
      return;
    }
  }

  writeLog("markFolderEnumerated: ID not found: " + id); 
}

function createFolderEntry(folder) {
  var folderData = [];
  var lastRow = statusSheet.getLastRow();
  var data = statusSheet.getDataRange().getValues();

  writeLog("Enumerating " + getFolderName(folder) + "/" + folder.getName());

  var logged = false;

  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === folder.getId() && data[i][5] === "") { 
      writeLog("Already logged " + folder.getId());
      logged = true;
      break;
    }
  }

  if (!logged) {
    folderData.push([folder.getId(), folder.getUrl(), getFolderName(folder) + "/" + folder.getName(), getCurrentUTCDateTime()]);
    statusSheet.getRange(lastRow + 1, 1, folderData.length, 4).setValues(folderData); 
  }
}

function processFolder(folder) {

  enumerateAndStoreFolders(folder);

  processFileOrFolder(folder);

  var token = null;
  var contData = continuationSheet.getDataRange().getValues();
  var contTokenRow = null;
  var contRowIndex = null;

  for (var i = 0; i < contData.length; i++) {
    if (contData[i][0] === folder.getId() && !contData[i][3] && contData[i][6] === "") { 
      contTokenRow = contData[i];
      contRowIndex = i + 1;
      token = contData[i][5];

      writeLog(`Found file token for folder ${folder.getName()} ${token}`);
      break;
    }
  }

  var files = null;

  if (token) {
    writeLog("Got a token, getting files");
    files = DriveApp.continueFileIterator(token);
  }

  if (!files) {
    if (token) {
      writeLog(`File token not valid for ${folder.getName()}, getting files again`);

      markContinueDone(folder.getId(), token, false);

      token = null;
    }
    files = folder.getFiles();

    var token = files.getContinuationToken();

    contTokenRow = [folder.getId(), folder.getUrl(), getFolderName(folder) + "/" + folder.getName(), false, getCurrentUTCDateTime(), token];
    contRowIndex = continuationSheet.getLastRow() + 1;
    continuationSheet.getRange(contRowIndex, 1, 1, contTokenRow.length).setValues([contTokenRow]);
  }

  while (files.hasNext()) {
    var file = files.next();
    processFileOrFolder(file);

    if (contTokenRow) {
      token = files.getContinuationToken();
      contTokenRow[5] = token;

      writeLog(`New file cont token: ${token}`);

      if (contRowIndex) {
        continuationSheet.getRange(contRowIndex, 1, 1, contTokenRow.length).setValues([contTokenRow]);
      }
    }
  }

  markFolderDone(folder.getId());
  markContinueDone(folder.getId(), token, false);
}

function processFileOrFolder(file) {

  var folderName = getFolderName(file);
  var getName = file.getName();
  var url = file.getUrl();
  writeLog(`Processing: ${folderName}/${getName} ${url}`);

  var description = file.getDescription();
  
  if (!description) {
    description = '';
  }

  if (description && description.includes(SEARCH_VALUE)) {
    writeLog("... skipping - description checked");
    return;
  }

  var originalModifiedTime = DriveApp.getFileById(file.getId()).getLastUpdated();

  if (description === '') {
    file.setDescription(SPECIAL_VALUE);
  } else {
    file.setDescription(description + "\n\n" + SPECIAL_VALUE);
  }

  resetModifiedTime(file.getId(), originalModifiedTime);
}

function resetModifiedTime(fileId, time) {
  var url = "https://www.googleapis.com/drive/v3/files/" + fileId;
  
  var params = {
    method: "patch",
    headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
    payload: JSON.stringify({modifiedTime: time.toISOString() }),
    contentType: "application/json",
  };

  UrlFetchApp.fetch(url, params);
}

function getFolderName(file) {
  var folders = file.getParents();

  // Assuming the file is only in one folder:
  if (folders.hasNext()) {
    var parentFolder = folders.next();
    return getFolderName(parentFolder) + "/" + parentFolder.getName();
  } else {
    return ""; 
  }
}

function markContinueDone(folderId, token, isFolders) {

  writeLog(`Marking token finished for folderId and token ${folderId} ${token}`);

  var contData = continuationSheet.getDataRange().getValues();

  for (var i = 0; i < contData.length; i++) {
    if (contData[i][0] == folderId && contData[i][3] == isFolders && contData[i][5] === token && contData[i][6] === "") { 
      
      contData[i][6] = getCurrentUTCDateTime();
      continuationSheet.getRange(i + 1, 1, 1, contData[i].length).setValues([contData[i]]);
      return;
    }
  }
}

function enumerateAndStoreFolders(folder) {
  
  var data = statusSheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === folder.getId() && data[i][4] != "" && data[i][5] === "") { 
      writeLog("Already enumerated " + folder.getId());
      return;
    }
  }

  var contData = continuationSheet.getDataRange().getValues();
  var token = null;
  var contTokenRow = null;
  var contRowIndex = null;

  for (var i = 0; i < contData.length; i++) {
    if (contData[i][0] === folder.getId() && contData[i][3] && contData[i][6] === "") { 
      contTokenRow = contData[i];
      contRowIndex = i + 1;
      token = contData[i][5];

      writeLog(`Found folder token for folder ${folder.getName()} ${token}`);
      break;
    }
  }

  var folders = null;

  if (token) {
    writeLog("Got a folder token, getting folders");
    folders = DriveApp.continueFolderIterator(token);  
  }
  
  if (!folders) {
    if (token) {
      writeLog(`Token not valid for ${folder.getName()}, getting folders again`);

      markContinueDone(folder.getId(), token, true);

      token = null;
    }

    folders = folder.getFolders();
    var token = folders.getContinuationToken();

    contTokenRow = [folder.getId(), folder.getUrl(), getFolderName(folder) + "/" + folder.getName(), true, getCurrentUTCDateTime(), token];
    contRowIndex = continuationSheet.getLastRow() + 1;
    continuationSheet.getRange(contRowIndex, 1, 1, contTokenRow.length).setValues([contTokenRow]);
  }

  while (folders.hasNext()) {
    var nextFolder = folders.next();

    createFolderEntry(nextFolder);

    if (contTokenRow) {
      token = folders.getContinuationToken();
      contTokenRow[5] = token;

      writeLog(`New folder cont token: ${token}`);

      if (contRowIndex) {
        continuationSheet.getRange(contRowIndex, 1, 1, contTokenRow.length).setValues([contTokenRow]);
      }
    }
  }

  markContinueDone(folder.getId(), token, true);

  markFolderEnumerated(folder.getId());
}