0

Currently, I have a script running that will scan Gmail for specific .csv files and add them to a specified folder in drive which works. I'm trying to take the data from those files as strings and put them into empty Sheets files so some of our third party apps can access the data. I can get the data from the files as strings, but I can't get the strings into a Sheets file.

I looked around found this thread (Create a new sheet in a Google Sheets with Google Apps Script) which details some methods for creating new spreadsheets and this blog post (https://spreadsheet.dev/how-to-import-csv-files-into-google-sheets-using-apps-script#:~:text=Your%20script%20will%20insert%20a,That's%20it!) that details steps for creating a menu to import data into an empty sheet manually. I tried to frankenstein together some of the ideas into my own code, but it isn't producing any new sheets. Here is the code that I'm running

function FromGmailtoDriveasSheet() {
var threads = GmailApp.getInboxThreads(0, 100); 
var msgs = GmailApp.getMessagesForThreads(threads); 
for (var i = 0 ; i < msgs.length; i++) {
  for (var j = 0; j < msgs[i].length; j++) {
    var attachments = msgs[i][j].getAttachments();
    for (var k = 0; k < attachments.length; k++) {
      var name = msgs[i][j].getSubject();
      var split = name.split(' ');
      var sender = msgs[i][j].getFrom()
      if (split[0] === 'PARAMETER'&& sender === 'SENDER EMAIL'){
        var folder = DriveApp.getFolderById('FOLDER ID');;
        var att_string =  attachments[k].getDataAsString();
        var files = folder.getFiles();
        var att_name = attachments[k].getName();
        var files_arr = [];
//the section below populates an array of files in the folder to check if the file the script is looking to //import isn't already in the folder
        while (files.hasNext()){
          var file = files.next();
          var fname = file.getName();
          files_arr.push(fname)
        }
        if(files_arr.includes(att_name)) continue;
        else{
          //REFERENCE ELSE STATMENT
          var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
          var newSheet = activeSpreadsheet.insertSheet();
          newSheet.setName(att_name);
          var contents = Utilities.parseCsv(att_string)
          newSheet.getRange(1,1,contents.length,contents[0].length).setValues(contents)
        }
    }
  }
}
}
}

I'm sure the problem is in the else statement at the end, as it isn't creating any Sheet files anywhere. I think I have to specify that the sheets need to be created in the specific folder, but I'm not sure how to do that.

Patrick McGavick
  • 11
  • 1
  • SpreadsheetApp.create() – Cooper Apr 12 '23 at 19:44
  • tried to implement that like this ``` else{ activeSpreadsheet = Spreadsheetapp.create(att_name) newSheet = activeSpreadsheet.insertSheet() var contents = Utilities.parseCsv(att_string) newSheet.getRange(1,1,contents.length,contents[0].length).setValues(contents) } ``` no dice. Doesn't even create a spreadsheet in the root drive. Any recommended fixes? – Patrick McGavick Apr 12 '23 at 20:07
  • @PatrickMcGavick: `Doesn't even create a spreadsheet in the root drive` - did you use the debugger and see if there is any errors? - also, does your `appsscript.json` contains the `oauthScopes` required? – Marco Aurelio Fernandez Reyes Apr 12 '23 at 21:21
  • For more clarity, what is the step-by-step process you want to achieve? E.g. *1.* Create an empty Spreadsheet file _(will this be for the saved **CSV** from your Drive? Or directly from the currently scanned email?)_ *2.* Place the **CSV** data etc... Please edit your post & give **more details** for your post to get good answers. Please **see** (How do I ask a good question?)[https://stackoverflow.com/help/how-to-ask] – SputnikDrunk2 Apr 13 '23 at 00:29

1 Answers1

0

This worked for me

function myfunk9() {
  let ss = SpreadsheetApp.create("New", 1, 1);
  let sh = ss.getSheets()[0];
  sh.getRange("A1").setValue(ss.getName());
  Logger.log(ss.getId())
}

It created the file in the root

Cooper
  • 59,616
  • 6
  • 23
  • 54