CODEWITHSUNDEEP
google-apps-script
Bldjef
Bldjef

Reputation: 138

Optimize Google Scripts - Triggers not working

Problem

My Google Script triggers aren't running because it "uses to much CPU time", according to error emails I receive. How can I optimize my scripts to use less CPU time? I've separated sortGsheetFiles into different trigger, but it still uses to much time. It used to be combined with the importXLSXtoGsheet function.

Scripts explained

I've got 52 folders, each containing one spreadsheet file.
Each folder is shared with different colleagues. During the day, people make changes to the files.

  1. At the end of the day, all files are collected in one folder (gsheetFolder) and converted to XLSX files, using the function collectAndExportXLS.

These files are copied to a local server in the evening (using batch script and drive sync) which updates other information in the file and are copied back to the importXLSXfolder.

  1. In the morning the importXLSXtoGsheet function runs and converts all XLSX files in the importXLSXfolder folder to Gsheet files in the gsheetFolder.
  2. After that sortGsheetFiles runs, sorting and moving every Gsheet file in one of the 52 folders (using an array list from the current spreadsheet).

Other actions include cleaning the folders with the deleteFolder function.

Triggers

importXLSXtoGsheet - every day - between 6 am and 7 am
sortGsheetFiles - every day - between 7 am and 8 am
collectAndExportXLS - every day - between 10 pm and 11 pm

Script

var gsheetFolder = 'xxx';
var XLSXfolder = 'xxxxx';
var importXLSXfolder = 'xxxxx';

function checkEmptyFolder() {

var folders = DocsList.getAllFolders()
  for(n=0;n<folders.length;++n){
    if(folders[n].getFiles().length==0 && folders[n].getFolders().length==0){
     folders[n].setTrashed(true)
     Logger.log(folders[n].getName())
     }
   }  
}

function importXLSXtoGsheet(){

// ========= convert all XLS files in XLS folder to GSheet and put in the general gsheet folder - after that sort in gsheet filiaal folders =========
// cleanup exportXLS folder first 
  deleteFolder(XLSXfolder);

  var files = DriveApp.getFolderById(importXLSXfolder).searchFiles('title contains ".xlsx"');
  while(files.hasNext()) {
    var xFile = files.next();
    var name = xFile.getName();
    if (name.indexOf('.xlsx')) { 
      var ID = xFile.getId();
      var xBlob = xFile.getBlob();
      var newFile = {
        title : name + ('.xlsx'),
        key : ID,
        parents: [{"id": gsheetFolder}]
      }
      file = Drive.Files.insert(newFile, xBlob, {convert: true});
    }
  }
  deleteFolder(importXLSXfolder);
}

function sortGsheetFiles() {

  // ========= sort Gsheet folder and move to corresponding filiaal folders =========

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var myArrayFileName = sheet.getRange("A2:A53").getValues();
  var myArrayFolderId = sheet.getRange("B2:B53").getValues();
  var a = myArrayFileName.join().split(',').filter(Boolean);
  var b = myArrayFolderId.join().split(',').filter(Boolean);

  var folderId = gsheetFolder;
  // Log the name of every file in the folder. 
  var files = DriveApp.getFolderById(folderId).getFiles();

     while (files.hasNext()) {
      var file = files.next();
        for (var i in a) {
          var id = file.getId();
          if (file.getName() == a[i]) { 
            moveFiles(id, b[i]); // Match found and move to corresponding folder
          }
        }
     }
  deleteFolder(importXLSXfolder);
}

function collectAndExportXLS() {
  // ========= collect all Gsheet files, copy to gsheet folder and convert to xlsx and move to xlsx folder =========

  // cleanup gsheet folder
  deleteFolder(gsheetFolder);

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var myArrayFileName = sheet.getRange("A2:A53").getValues();
  var myArrayFolderId = sheet.getRange("B2:B53").getValues();
  var a = myArrayFileName.join().split(',').filter(Boolean);
  var b = myArrayFolderId.join().split(',').filter(Boolean);

  var folderId = gsheetFolder;

  for (var i in b) {
  var files = DriveApp.getFolderById(b[i]).getFiles();
    while (files.hasNext()) {
      var file = files.next();
      var id = file.getId();
      moveFiles(id , folderId);
    }
  }
  ConvertBackToXLS()
  deleteFolder(gsheetFolder);

}

function moveFiles(sourceFileId, targetFolderId) {
  var file = DriveApp.getFileById(sourceFileId);
  file.getParents().next().removeFile(file);
  DriveApp.getFolderById(targetFolderId).addFile(file);
}

function deleteFolder(folder) {
  //delete files in a folder without sending to trash!

  var eachFile, idToDLET, myFolder, rtrnFromDLET, thisFile, files;
  files = DriveApp.getFolderById(folder).getFiles();

  while (files.hasNext()) {//If there is another element in the iterator
    eachFile = files.next();
    idToDLET = eachFile.getId();
    //Logger.log('idToDLET: ' + idToDLET);

    rtrnFromDLET = Drive.Files.remove(idToDLET);
  };
   Logger.log('folder deleted');
}

function ConvertBackToXLS() {

  // Log the name of every file in the folder.
  var files = DriveApp.getFolderById(gsheetFolder).getFiles();
  var dir = DriveApp.getFolderById(XLSXfolder);
  while (files.hasNext()) {

     try {
     var file = files.next(); 
     var ss = SpreadsheetApp.openById(file.getId());
     Logger.log(file.getId());
     var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + file.getId() + "&exportFormat=xlsx";
     var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();
    blob.setName(ss.getName());  
    var newfile = dir.createFile(blob); 

  } catch (f) {
    Logger.log(f.toString());
  }
 }
}

Upvotes: 1

Views: 212

Answers (1)

Tanaike
Tanaike

Reputation: 201388

How about this modification? Please think of this as just one of several answers.

And as an important point, please test the script using a test situation, before you run with your actual situation.

About your script:

  1. collectAndExportXLS(): Abou this function name, I didn't modify collectAndExportXLS(). Because I thought that you might be using this function name at other script or triggers.

    1. Delete all files in gsheetFolder.
    2. Convert a Google Spreadsheet in each folder ID retrieved from "B2:B53" of the sheet with the 1st index in the active Spreadsheet to XLSX format.
      • Filename is like sample.xlsx.
      • All converted files are put in XLSXfolder.
    3. Delete all files in all folder IDs.
    4. XLSX files in XLSXfolder are put in importXLSXfolder by other script.

  2. importXLSXtoGsheet(): Abou this function name, I didn't modify collectAndExportXLS(). Because I thought that you might be using this function name at other script or triggers.

    1. Delete all files in XLSXfolder.
    2. Convert all XLSX files in importXLSXfolder to Google Spreadsheet.
      • Filename is like sample.xlsx.
      • Converted Google Spreadsheets are put in gsheetFolder.
    3. Delete all files in importXLSXfolder.

  3. sortGsheetFiles()

    1. Move Google Spreadsheets in gsheetFolder to each folder ID retrieved from "B2:B53" of the sheet with the 1st index in the active Spreadsheet.
      • In order to match the folder ID, the filenames of Google Spreadsheet and the values retrieved from "A2:A53".
    2. Delete all files in importXLSXfolder.

  4. I understood that from your question, the filenames of column "A2:A53" of the active Spreadsheet are the same with the filenames of Google Spreadsheets which were put in the folders of folder IDs of the column "B2:B53".

  5. I understood that the number of all files is less than 100.

I understand like above. If my understanding is correct, how about this modification? In my modification, I used the Batch request of Drive API and the fetchAll method of UrlFetchApp with the type of multipart/form-data for your situation. The batch request and fetchAll method can work with the asynchronous process. By this, I thought that your process cost might be reduced.

In order to use these methods, I used 2 GAS libraries. Before you run the script, please install these 2 libraries for your script. You can see how to install the library as follows.

  1. Install a library for running the fetchAll method of UrlFetchApp with the type of multipart/form-data.
  2. Install a library for running batch request.

Modification points:

  • collectAndExportXLS()

    • File IDs in each folder are retrieved by the batch request.
    • Blobs (XLSX format) from each file ID are retrieved by the fetchAll method of UrlFetchApp.
    • Files of XLSX format are created by FetchApp.

  • importXLSXtoGsheet()

    • File list is retrieved by the files.list method of Drive API.
    • Files of XLSX format are converted to Google Spreadsheet by the batch request.

  • sortGsheetFiles()

    • File list is retrieved by the files.list method of Drive API.
    • Files of Google Spreadsheet are moved to each folder ID retrieved from the column "B2:B53" of the active Spreadsheet using the batch request.

  • deleteFolder()

    • Files in the folder are deleted by the batch request.

When above points are reflected to your script, it becomes as follows.

Modified script:

After installed 2 libraries, please run the following script.

var gsheetFolder = '###';
var XLSXfolder = '###';
var importXLSXfolder = '###';

// Modified
function deleteFolder(folderId) {
  var url = "https://www.googleapis.com/drive/v3/files?q='" + folderId + "'+in+parents+and+trashed%3Dfalse&fields=files%2Fid&access_token=" + ScriptApp.getOAuthToken();
  var res = UrlFetchApp.fetch(url);
  var obj = JSON.parse(res.getContentText());
  var reqs = obj.files.map(function(e) {return {method: "DELETE", endpoint: "https://www.googleapis.com/drive/v3/files/" + e.id}});
  var requests = {batchPath: "batch/drive/v3", requests: reqs};
  if (requests.requests.length > 0) BatchRequest.Do(requests);
}

// Added
function deleteFiles(files) {
  var reqs = files.map(function(e) {return {method: "DELETE", endpoint: "https://www.googleapis.com/drive/v3/files/" + e.id}});
  var requests = {batchPath: "batch/drive/v3", requests: reqs};
  if (requests.requests.length > 0) BatchRequest.Do(requests);
}

// Added
function getValuesFromSpreadsheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  return sheet.getRange("A2:B53").getValues();
}

// Modified
function sortGsheetFiles() {
  var url = "https://www.googleapis.com/drive/v3/files?q='" + gsheetFolder + "'+in+parents+and+mimeType%3D'" + MimeType.GOOGLE_SHEETS + "'+and+trashed%3Dfalse&fields=files(id%2Cname)&access_token=" + ScriptApp.getOAuthToken();
  var res = UrlFetchApp.fetch(url);
  var obj = JSON.parse(res.getContentText());
  var values = getValuesFromSpreadsheet();
  var reqs = values.reduce(function(ar, e) {
    for (var i = 0; i < obj.files.length; i++) {
      if (obj.files[i].name == e[0]) {
        ar.push({
          method: "PATCH",
          endpoint: "https://www.googleapis.com/drive/v3/files/" + obj.files[i].id + "?addParents=" + e[1] + "&removeParents=" + gsheetFolder,
        });
        break;
      }
    }
    return ar;
  }, []);
  var requests = {batchPath: "batch/drive/v3", requests: reqs};
  if (requests.requests.length > 0) BatchRequest.Do(requests);
  deleteFolder(importXLSXfolder);
}

// Modified
function importXLSXtoGsheet(){
  deleteFolder(XLSXfolder);
  var url = "https://www.googleapis.com/drive/v3/files?q='" + importXLSXfolder + "'+in+parents+and+mimeType%3D'" + MimeType.MICROSOFT_EXCEL + "'+and+trashed%3Dfalse&fields=files(id%2Cname)&access_token=" + ScriptApp.getOAuthToken();
  var res = UrlFetchApp.fetch(url);
  var obj = JSON.parse(res.getContentText());
  var reqs = obj.files.map(function(e) {return {
      method: "POST",
      endpoint: "https://www.googleapis.com/drive/v3/files/" + e.id + "/copy",
      requestBody: {mimeType: MimeType.GOOGLE_SHEETS, name: e.name + ".xlsx", parents: [gsheetFolder]},
    }
  });
  var requests = {batchPath: "batch/drive/v3", requests: reqs};
  if (requests.requests.length > 0) BatchRequest.Do(requests);
  deleteFolder(importXLSXfolder);
}

// Modified
function ConvertBackToXLS(fileList) {
  var token = ScriptApp.getOAuthToken();
  var reqs1 = fileList.map(function(e) {return {
      method: "GET",
      url: "https://docs.google.com/spreadsheets/export?id=" + e.id + "&exportFormat=xlsx&access_token=" + token,
    }
  });
  var res = UrlFetchApp.fetchAll(reqs1);
  var reqs2 = res.map(function(e, i) {
    var metadata = {name: fileList[i].name, parents: [XLSXfolder]};
    var form = FetchApp.createFormData(); // Create form data
    form.append("metadata", Utilities.newBlob(JSON.stringify(metadata), "application/json"));
    form.append("file", e.getBlob());
    var url = "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart";
    return {url: url, method: "POST", headers: {Authorization: "Bearer " + token}, body: form};
  });
  FetchApp.fetchAll(reqs2);
}

// Modified
function collectAndExportXLS() {
  deleteFolder(gsheetFolder);
  var values = getValuesFromSpreadsheet();
  var reqs1 = values.reduce(function(ar, e) {
    if (e[0] && e[1]) {
      ar.push({
        method: "GET",
        endpoint: "https://www.googleapis.com/drive/v3/files?q='" + e[1] + "'+in+parents+and+trashed%3Dfalse&fields=files(id%2Cname)",
      });
    }
    return ar;
  }, []);
  var resForReq1 = BatchRequest.Do({batchPath: "batch/drive/v3", requests: reqs1});
  var temp = resForReq1.getContentText().split("--batch");
  var files = temp.slice(1, temp.length - 1).map(function(e) {return JSON.parse(e.match(/{[\S\s]+}/g)[0])});
  var fileList = files.reduce(function(ar, e) {return ar.concat(e.files.map(function(f) {return f}))}, []);
  ConvertBackToXLS(fileList);
  deleteFiles(fileList);
}

Note:

  • In this modification, the error handling is not reflected, because I couldn't test your situation. So please add it, if you are required.
  • If the file size of XLSX files is large, the error might occur.

References:

Upvotes: 1

Related Questions