https://www.pbainbridge.co.uk/2022/11/bulk-convert-google-sheets-to-pdfs.html

Bulk convert Google Sheets to PDFs.gs

/*
Global Variables that may need to be tweaked and are easier to access by placing here.
*/


// Maximum runtime of script in minutes to prevent timeout (5 minutes 30 seconds)
var maxRuntime = 5.5 * 60 * 1000;


/*
This Function gets the necessary data from the spreadsheet.
*/

function getSpreadsheetData() {

  try {
    // Log information
    logEvent('Script has started');

    // get spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    // create toast popup to inform user
    ss.toast('Task has now started ...', 'Start');

    // get 'Welcome' sheet
    var welcomeSheet = ss.getSheetByName('Welcome');

    // get folder Id containing Sheets
    var sheetsFolderId = welcomeSheet.getRange(5, 4).getValue().trim();

    // get folder Id of destination folder to save PDFs into
    var pdfFolderId = welcomeSheet.getRange(9, 4).getValue().trim();

    // get value for deleting Sheets after conversion
    var deleteSheets = welcomeSheet.getRange(13, 4).getValue().trim();


    // create name:value pair so array easier to work with
    var sheetData = { ss: ss, sheetsFolderId: sheetsFolderId, pdfFolderId: pdfFolderId, deleteSheets: deleteSheets };

    // run Function to convert Sheets to PDFs
    var convertToPdfFlag = convertToPdf(sheetData);


    if (convertToPdfFlag) {
      // no errors occurred during process

      // Log information
      logEvent('Script has finished without errors');
    }
    else {
      // there were errors during process, relevant popup will already be displayed

      // Log information
      logEvent('Script completed with errors');
    }
  }
  catch (error) {
    // there was a problem getting spreadsheet data
    logEvent("Error with 'getSpreadsheetData' Function: " + error);

    // create popup to inform user
    var popupTitle = "'getSpreadsheetData' Function Error";
    var popupMessage = "Error message: " + error;
    htmlPopup(popupTitle, popupMessage);
  }


}


/*
This Function gets the Google Drive folder containing the files.
It iterates through each file and only handles the Google Sheets.
It creates a PDF version of the Sheet and saves this into the given Drive folder.
It then optionally deletes the original Google Sheet if the user selected this.
*/

function convertToPdf(sheetData) {

  try {

    // Log information
    logEvent("Starting 'convertToPdf' Function");

    // extract Sheet data from name:value pair array
    var ss = sheetData['ss'];
    var sheetsFolderId = sheetData['sheetsFolderId'];
    var pdfFolderId = sheetData['pdfFolderId'];
    var deleteSheets = sheetData['deleteSheets'];

    // Log information
    logEvent("Option to delete original Google Sheets is: " + deleteSheets);

    // get Google Drive folder containing files to convert
    var sheetsFolder = DriveApp.getFolderById(sheetsFolderId);

  }
  catch (error) {
    // error getting Google Drive folder
    logEvent('Error getting Google Drive folder containing files to convert: ' + error);

    // create popup to inform user
    var popupTitle = "Error getting Google Drive folder";
    var popupMessage = "Error message: " + error;
    htmlPopup(popupTitle, popupMessage);

    // return Flag to Parent Function
    return false;
  }


  try {
    // proceed IF successfully got Google Drive folder containing files to convert
    if (sheetsFolder) {

      // Log information
      logEvent("Starting to convert files ...");

      // create Counter to log number of converted files
      var convertedCounter = 0;


      // run Function to check Script Properties for 'Continuation Token'
      var continuationToken = getScriptProperties();

      // check no error getting Script Properties before proceeding ************************
      if (continuationToken !== false) {

        // get start time so can manage duration of script and avoid timeout
        var startTime = new Date().getTime();

      }
      else {
        // error occurred, return false flag to Parent Function
        return false;
      }
      // check no error getting Script Properties before proceeding ************************


      // check if 'Continuation Token' exists to determine if continuing conversion
      if (continuationToken === null) {

        // no Token exists so proceed as first time running the script:

        // log message in sheet
        logEvent('No Continuation Token exists. Converting Docs ...');

        // get Google Sheet sub-files only
        // https://developers.google.com/apps-script/reference/base/mime-type
        var subFiles = sheetsFolder.getFilesByType(MimeType.GOOGLE_SHEETS);

      }
      else {

        // Token does exist in Script Properties

        // log message in sheet
        logEvent('Continuation Token does exist. Converting Sheets ...');

        // continue iterating through Drive files with Token
        var subFiles = DriveApp.continueFileIterator(continuationToken);

      }


      // cycle through each sub-file
      while (subFiles.hasNext()) {

        // get sub-file
        var subFile = subFiles.next();

        // get sub-file name
        var subFileName = subFile.getName();

        // create toast popup to inform user
        ss.toast('File: ' + subFileName, 'Converting');


        // create PDF version of Sheet ****************************************
        var blobFile = subFile.getAs('application/pdf');
        blobFile.setName(subFileName);  // set name specifically as may lose it if comprehensive
        var pdfVersion = DriveApp.createFile(blobFile);

        // get Id of PDF version
        var pdfVersionId = pdfVersion.getId();

        // get PDFs destination folder
        var pdfFolder = DriveApp.getFolderById(pdfFolderId);

        // get PDF file and move to new location
        DriveApp.getFileById(pdfVersionId).moveTo(pdfFolder);
        // create PDF version of Doc ****************************************


        // increment Counter by 1 to log number of converted files
        convertedCounter++;

        // check IF user selected to delete the original Google Sheet
        if (deleteSheets == 'Yes') {
          // delete original Sheet file
          subFile.setTrashed(true);
        }
        else {
          // user did not select to delete original Sheet, so do nothing
        }


        // *************************** perform runtime check ***************************
        // get current time
        var endTime = new Date().getTime();

        // find elapsed time by subtracting from start time
        var elapsedTime = endTime - startTime;

        // check against maximum runtime
        var timeLimitExceeded = elapsedTime >= maxRuntime;


        // check status of runtime
        if (timeLimitExceeded) {

          // runtime has been met/exceeded, store 'Continuation Token' so can resume later
          // log message in sheet
          logEvent('Runtime has been met/exceeded.');

          // get Token from last file
          var continuationToken = subFiles.getContinuationToken();


          /*
          run Function to add Token to Script Properies for use later.
          capture in error variable as this is the last step in process anyway,
          so no matter what will be using 'break'
          */
          setPropertyFlag = setScriptProperties(continuationToken);

          if (setPropertyFlag) {
            // successfully completed

            // break out of loop to prevent script from continuing
            break;
          }
          else {
            // problem occurred, return false Flag
            return false;
          }

        }
        else {
          // runtime has not been met/exceeded, script can continue looping through files
        }
        // *************************** perform runtime check ***************************


      }

      // Log information
      logEvent("Completed converting " + convertedCounter + " Sheets in folder");

    }
    else {
      // do nothing as script will have terminated at this point
    }


    // check if there are still files left to convert
    if (subFiles.hasNext()) {

      // yes there are files still left to convert

      // log message in sheet
      logEvent('Successfully created ' + convertedCounter + ' PDFs.');

      // log message in sheet
      logEvent('There are still Sheets to convert.');

      // no errors have occurred in the script
      var popupTitle = 'Runtime reached - ' + convertedCounter + ' PDFs created';
      var popupMessage = 'No errors, but there are Sheets still to create PDFs for, so you may wish to run this tool again.';
      htmlPopup(popupTitle, popupMessage);

      // log message in sheet
      logEvent('Script completed without errors.');

      // return success Flag
      return true;

    }
    else {

      // no all files have been converted

      // delete Script Properties of Token as no longer required
      deletePropertyFlag = deleteScriptProperties('CONTINUATION_TOKEN');

      // check no error deleting Script Property before proceeding ***********
      if (deletePropertyFlag) {

        // log message in sheet
        logEvent('All Sheets have been converted.');

        // no errors have occurred in the script
        var popupTitle = 'Tool complete - ' + convertedCounter + ' PDFs created';
        var popupMessage = 'No errors. PDFs have been created for all Google Sheets.';
        htmlPopup(popupTitle, popupMessage);

        // return success Flag
        return true;

      }
      else {
        // error deleting Script Property - do nothing as popup will already be displayed
      }
      // check no error deleting Script Properties before proceeding ***********

    }

  }
  catch (error) {
    // there was a problem during PDF conversion
    logEvent('Error during file iteration/conversion: ' + error);

    // create popup to inform user
    var popupTitle = "Error during PDF conversion";
    var popupMessage = "Error message: " + error;
    htmlPopup(popupTitle, popupMessage);

    return false;
  }

}


/*
Function to look for 'Continuation Token' in Script Properties.

https://developers.google.com/apps-script/guides/properties
*/

function getScriptProperties() {

  try {
    // log message in sheet
    logEvent('Getting Script Properties.');

    // access Script Properties
    var scriptProperties = PropertiesService.getScriptProperties();

    // look for 'Continuation Token' Property
    var continuationToken = scriptProperties.getProperty('CONTINUATION_TOKEN');

    // return value to Parent Function
    return continuationToken;
  }
  catch (error) {
    // log error in sheet
    logEvent('Problem getting Script Properties: ' + error);

    // display user popup to inform of error
    var popupTitle = 'Get Script Properties error';
    var popupMessage = 'Problem getting Script Properties: ' + error;
    htmlPopup(popupTitle, popupMessage);

    // return false to Parent Function
    return false;
  }

}


/*
Function to set 'Continuation Token' in Script Properties.

https://developers.google.com/apps-script/guides/properties
*/

function setScriptProperties(continuationToken) {

  try {
    // log message in sheet
    logEvent('Setting Script Properties.');

    // access Script Properties
    var scriptProperties = PropertiesService.getScriptProperties();

    // set 'Continuation Token'  Script Property
    var continuationToken = scriptProperties.setProperty('CONTINUATION_TOKEN', continuationToken);

    // log message in sheet
    logEvent('Successfully set Script Properties.');

    // return true to Parent Function
    return true;
  }
  catch (error) {
    // log error in sheet
    logEvent('Problem setting Script Property: ' + error);

    // display user popup to inform of error
    var popupTitle = 'Set Script Property error';
    var popupMessage = 'Problem setting Script Property: ' + error;
    htmlPopup(popupTitle, popupMessage);

    // return false to Parent Function
    return false;
  }

}


/*
Function to delete 'Continuation Token' in Script Properties.

https://developers.google.com/apps-script/guides/properties
*/

function deleteScriptProperties(property) {

  try {
    // log message in sheet
    logEvent('Deleting Script Properties.');

    // access Script Properties
    var scriptProperties = PropertiesService.getScriptProperties();

    // delete 'Continuation Token' Property
    scriptProperties.deleteProperty(property);

    // log message in sheet
    logEvent('Successfully deleted Script Properties.');

    // return true to Parent Function
    return true;
  }
  catch (error) {
    // log error in sheet
    logEvent('Problem deleting Script Property: ' + error);

    // display user popup to inform of error
    var popupTitle = 'Delete Script Property error';
    var popupMessage = 'Problem deleting Script Property Token: ' + error;
    htmlPopup(popupTitle, popupMessage);

    // return false to Parent Function
    return false;
  }

}


/*
Reset all data within Google Sheet to be able to start afresh.
*/

function reset() {

  try {
    // log that reset Function has been selected
    logEvent("Initiated 'Reset' Function");

    // get Spreadsheet UI
    var ui = SpreadsheetApp.getUi();

    // create Alert popup box
    var result = ui.alert(
      'Reset spreadsheet',
      'Do you wish to remove all inputted information to start again?',
      ui.ButtonSet.OK_CANCEL
    );


    // process user response from Popup Box
    if (result == ui.Button.OK) {

      // log selection
      logEvent("Selected 'OK' to reset spreadsheet");


      // get current spreadsheet
      var ss = SpreadsheetApp.getActiveSpreadsheet();

      // get Welcome sheet
      var welcomeSheet = ss.getSheetByName('Welcome');

      // clear source folder ID box
      welcomeSheet.getRange(5, 4).clearContent();

      // clear destination folder ID box
      welcomeSheet.getRange(9, 4).clearContent();

      // clear delete select box
      welcomeSheet.getRange(13, 4).clearContent();


      // delete Script Properties of Token
      deletePropertyFlag = deleteScriptProperties('CONTINUATION_TOKEN');


      // log completion of Function
      logEvent("Completed 'Reset' Function.");

      // display alert popup to user to confirm completion
      ui.alert('Successfully cleared all sheet data.');

    }
    else {

      // User clicked 'No' or 'X' in title bar so do nothing
      logEvent("Selected 'CANCEL' to reset spreadsheet");

    }
  }
  catch (error) {

    // problem clearing sheet data
    logEvent("Problem with 'reset' Function" + error + error.stack);

    // run Function to launch HTML popup
    var popupTitle = "'reset' Function error";
    var popupMessage = "Unable to clear sheet data: " + error;
    htmlPopup(popupTitle, popupMessage);

  }

}


/*
Function to output messages to the 'Log' sheet.
Can be called anywhere else in script.
*/

function logEvent(action) {

  // get the user running the script
  var theUser = Session.getActiveUser().getEmail();

  // get the relevant spreadsheet to output log details
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet = ss.getSheetByName('Log');

  // create and format a timestamp
  var dateTime = new Date();
  var timeZone = ss.getSpreadsheetTimeZone();
  var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ HH:mm:ss");

  // create array of data for pasting into log sheet
  var logData = [niceDateTime, theUser, action];

  // append details into next row of log sheet
  logSheet.appendRow(logData);

}


/*
This Function creates a menu item to run this script.
*/

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Admin')
    .addItem('Reset sheet', 'reset') // label for menu item, name of function to run.
    .addToUi();

}


/*
Display a modal dialog box with custom HtmlService content.
Does not suspend the script.
*/

function htmlPopup(popupTitle, popupMessage) {

  var htmlOutput = HtmlService
    .createHtmlOutput(popupMessage)
    .setWidth(360)
    .setHeight(180);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, popupTitle);

}