0

I am trying to find an app script solution to automatically import an Excel file into my Google Sheet each morning. I have found a number of examples on this forum as to how to import an Excel file however I have two challenges that I have not been able find a solution for:

  1. The filename of the Excel sheet I need to import changes daily as it is an inventory report from the night prior and the filename is based on the date and time the report was run.

  2. Only the first 10 of the 14 characters of the filename are predictable. The filename is always "IV" followed by the year, month, day and the time the report ran however the time that the report runs is not consistent. For example IV201907142308.xls means the report ran July 14, 2019 at 11:08pm however the night before the report ran at 11:04pm.

Possible workaround:

Instead of trying to import an Excel file from a specified folder by matching only part of the filename I could have another sheet that runs a script listing all files in a folder and the link to each file and have the import Excel file script refer to that list in order to find the link to the correct Excel file. I can create the list all files script but I am not sure how to have the import Excel file script refer to a link on the sheet in order to upload the correct Excel file.

How do I solve the problem?

Community
  • 1
  • 1
micah
  • 13
  • 1
  • You can upload them all to the same folder and then put all their names into an array and sort them with a compare function that compares the date embedded in their name. Make the function put them in descending order and always take the first one in the array. Or you could use a custom upload program that always deletes all of the other files in the folder. There a lot of ways to go. – Cooper Jul 20 '19 at 03:58
  • Thanks for weighing in. The reports are all generated in the same folder however deleting them is not an option as the records need to be store for future reference. The hiccup is still the two parts though; 1. I have not been able to find an auto upload script that uses a cell reference to determine the file name or; 2. Cannot find a way to auto upload the with the filename based on a string that is composed of "INV"+yesterdays date+a wild card for the time. – micah Jul 20 '19 at 04:56
  • Additionally I have discovered that my upload script is now depreciated. – micah Jul 20 '19 at 04:57
  • 1
    As far as the upload is concerned I would use an HTML user interface either a dialogue or a web app and use the input type=“file” and in that case it doesn’t matter what the file name is because a user will have to pick the file from your computer which is then uploaded to the server so doesn’t matter what the file name is because you’ll have to pick it. – Cooper Jul 20 '19 at 15:09
  • Cooper, I considered that option as well but am trying to find an automated solution that will work on a time trigger as the end user is not always very computer literate. – micah Jul 24 '19 at 02:31

1 Answers1

0

Actually, you don't even need your file's name to import it. Since you mention the time can change and doesn't matter, I'm assuming only one file is generated per day.

If it's indeed the case, you can find it by it 'created date' by comparing it to yesterday's date.

function importYesterdayReport() {

  var folders = DriveApp.getFoldersByName(/* your folder's name */);
  var files = folders.next().getFiles();

  //yesterday's date setup
  var date = new Date();
  date.setDate(date.getDate() - 1)
  var yesterday = date.toDateString(); //because time won't be the same

  //loop thru files to find yesterday's
  while(files.hasNext()){
    var file = files.next();
    date = file.getDateCreated();
    var fileDate = date.toDateString(); //because time won't be the same

    if(fileDate === yesterday){
      /* your import code */
    }
  }
}

getDateCreated() method

Date() JS ref

Other option id to search for your file title. Since the search criteria is 'contains' the fact that the hours change is not significant.

So you can replace the 'yesterday's date setup by :

var date = new Date();
var year = date.getFullYear();
var month = ("0"+(date.getMonth()+1)).slice(-2);
var day = date.getUTCDate();

and use this instead for var files : (will need to be after yesterday's date setup)

var searchCrit = 'title contains "IV'+year+month+day+'"'
var files = folders.next().searchFiles(searchCrit);

searchFiles() method

Salix
  • 1,290
  • 9
  • 15
  • for your depricated import script : https://ctrlq.org/code/20279-import-csv-into-google-spreadsheet – Salix Jul 20 '19 at 05:51
  • Salix, thanks for the link. I have tried the script previously but have not found a solution that works with excel files which is how the reports save. – micah Jul 20 '19 at 07:26
  • As for the script above, there are 3 different files saved each day however I believe that the file in question is the only one that is not saved into a subfolder which could alleviate the issue altogether. – micah Jul 20 '19 at 07:31
  • well if all the other files are in sub folder, it means that it's still only those file in a folder (with other folders?) so it should still work. If not, there's ways to search for files starting with IV. – Salix Jul 20 '19 at 13:15
  • for the xls file : https://stackoverflow.com/questions/26854563/how-to-automatically-import-data-from-uploaded-csv-or-xls-file-into-google-sheet did you see this? – Salix Jul 20 '19 at 13:18
  • Salix, I did see that post and am still trying to work through the error message received when trying it out. Request failed for https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true returned code 403. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "insufficientPermissions", "message": "Insufficient Permission: Request ... (use muteHttpExceptions option to examine full response) (line 23, file "TestImport") – micah Jul 24 '19 at 02:54
  • I'd need to see what's on line 23, but it seems to be a problem with permission? are those files yours or shared with you? – Salix Jul 24 '19 at 18:16
  • Could also be that you need to enable some advance setting – Salix Jul 24 '19 at 18:17
  • But, that's kind of another question. This one was about importing files daily with dynamic names. Which this solution was for. – Salix Jul 24 '19 at 18:20
  • @micah if you want help with the xls import, could you add your code that give you that error in your question? – Salix Jul 26 '19 at 15:16