1

I have:

  • .xlsx files being automatically imported on a daily basis to a google drive folder

I want:

  • each file converted to a CSV and saved to a different folder

  • each file converted to a Google Sheet and saved to a different folder

  • the original xlsx file not deleted

  • BUT only for files that have been added since I last ran the script

I pieced the below code together from this post "Unzip File Only if New in Google Apps Script" and from this post "Converting all files in drive folder from sheets to CSV and add to new folder". However I have an issue -- when I try to run this I am getting an error message

GoogleJsonResponseException: API call to drive.files.copy failed with error: File not found: [Name of SheetsFolder]

Can anyone tell what the error is by looking at this code?

function makeCSV() {
  //Add folder ID to select the folder where excel files are placed
  var SourceFolder = DriveApp.getFolderById("12XqAVzwzgl4a7Z1s6zn5VRcHiZ1vQdsT")
  //Add folder ID to save the where csv files to be placed
  var DestinationFolder = DriveApp.getFolderById("1Z_mUXs3FFDTN7XLk8EN9D5QOxVD8lZfB")
  //Add folder ID to save where the google sheets to be placed
  var SheetsFolder = DriveApp.getFolderById("17k_bl7Of2jH3U_TjaiiY_8BBOds4uSbT")

  //Select the XLS and XLSX files from source folder using the Mimetype of Microsoft Excel
  var searchQuery = "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'";
  var sourceFiles = SourceFolder.searchFiles(searchQuery);

  var now = new Date(); //get current time after you fetch the file list from Drive.

  //Get script properties and check for stored "last_execution_time"
  var properties = PropertiesService.getScriptProperties();
  var cutoff_datetime = properties.getProperty('last_execution_time');

  //if we have last execution date, stored as a string, convert it to a Date object.
  if(cutoff_datetime)
     cutoff_datetime = new Date(cutoff_datetime);

  //Loop over all the XLS files
  while (sourceFiles.hasNext()){
    var sourceFile = sourceFiles.next();

    //if no stored last execution, or file is newer than last execution, process the file.
    if(!cutoff_datetime || file.getDateCreated() > cutoff_datetime){
      var fileId = sourceFile.getId();

      //Copy the XLS file to the Sheets Folder as a google sheet 
      var spreadsheet = Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: SheetsFolder}]}, fileId);

      //Get the data from the google sheet using the SpreadsheetApp
      var sheets = SpreadsheetApp.openById(spreadsheet.id).getSheets();sheets[0].getDataRange().getValues()

      //Create a csv file using the data from the google sheet     
      var csv = sheets.map(function(sheet) {return sheet.getDataRange().getValues().reduce(function(csv, row) {return csv += row.join(",") + "\n"}, "")}); DestinationFolder.createFile(spreadsheet.title + ".csv", csv, MimeType.CSV)
    }
  }

  //store "now" as last execution time as a string, to be referenced on next run.
  properties.setProperty('last_execution_time',now.toString());
} 

Any help much appreciated!

EDIT 1: Thank you to Tanaike for helping to troubleshoot! The above code had two issues: one was the issue Tanaike points out in his comment. The second is that "file" should read "sourceFile" in the if statement. I am posting the correct code below for posterity.

function makeCSV() {
  //Add folder ID to select the folder where excel files are placed
  var SourceFolder = DriveApp.getFolderById("12XqAVzwzgl4a7Z1s6zn5VRcHiZ1vQdsT")
  //Add folder ID to save the where csv files to be placed
  var DestinationFolder = DriveApp.getFolderById("1Z_mUXs3FFDTN7XLk8EN9D5QOxVD8lZfB")
  //Add folder ID to save where the google sheets to be placed
  var SheetsFolder = DriveApp.getFolderById("17k_bl7Of2jH3U_TjaiiY_8BBOds4uSbT")

  //Select the XLS and XLSX files from source folder using the Mimetype of Microsoft Excel
  var searchQuery = "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'";
  var sourceFiles = SourceFolder.searchFiles(searchQuery);

  var now = new Date(); //get current time after you fetch the file list from Drive.

  //Get script properties and check for stored "last_execution_time"
  var properties = PropertiesService.getScriptProperties();
  var cutoff_datetime = properties.getProperty('last_execution_time');

  //if we have last execution date, stored as a string, convert it to a Date object.
  if(cutoff_datetime)
     cutoff_datetime = new Date(cutoff_datetime);

  //Loop over all the XLS files
  while (sourceFiles.hasNext()){
    var sourceFile = sourceFiles.next();

    //if no stored last execution, or file is newer than last execution, process the file.
    if(!cutoff_datetime || sourceFile.getDateCreated() > cutoff_datetime){
      var fileId = sourceFile.getId();

      //Copy the XLS file to the Sheets Folder as a google sheet 
      var spreadsheet = Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id:"17k_bl7Of2jH3U_TjaiiY_8BBOds4uSbT"}]}, fileId);

      //Get the data from the google sheet using the SpreadsheetApp
      var sheets = SpreadsheetApp.openById(spreadsheet.id).getSheets();sheets[0].getDataRange().getValues()

      //Create a csv file using the data from the google sheet     
      var csv = sheets.map(function(sheet) {return sheet.getDataRange().getValues().reduce(function(csv, row) {return csv += row.join(",") + "\n"}, "")}); DestinationFolder.createFile(spreadsheet.title + ".csv", csv, MimeType.CSV)
    }
  }

  //store "now" as last execution time as a string, to be referenced on next run.
  properties.setProperty('last_execution_time',now.toString());
} 
  • 2
    How about modifying from `parents: [{id: SheetsFolder}]}` to `parents: [{id: "17k_bl7Of2jH3U_TjaiiY_8BBOds4uSbT"}]}`? Because it is required to use the folder ID to `Drive.Files.copy` instead of the folder object. But I'm not sure whether your issue is only this. So I proposed this as a commented. – Tanaike Mar 13 '20 at 01:19
  • 1
    Thanks Tanaike! The code had two isses. 1.) the issue you mention and 2.) in the if statement should read "sourceFile.getDateCreated()". It works now! – BuckEyeTree Mar 13 '20 at 02:37

0 Answers0