How to write google script to automatically call data from a google folder drive that contains a CSV file into one Google Sheets

Question

I am trying to write a google script that will allow me to go into my google drive folder called "MeadJohsnon" and pull 'Temperature Calibration.csv' to google sheets. I have never used google script before. Currently I have the "Save Email and Attachment" Add-ons. This add-on is pulling .cvs files that my team is sending me from the field. They use "TapForms" and then send the form via email to my gmail. So, I got the email sending that attachment to my google drive but I need help with the script, so Drive will automatically get those .cvs files and put the information into one google sheet. I say ONE google sheet because although I have a team sending in forms, all the forms have the same information on them.

This is what I have done so far. The fourth line gives me a

function loadingCSV() {
 var ss=SpreadsheetApp.getActiveSpreadsheet()
 var sht=ss.getActiveSheet();
 sht.clearContents();
 var data = loadFile();
 var dataA =Utilities.parseCsv(data);
 var rng = sht.getRange(1, 1, dataA.length, dataA[0].length);
 rng.setValues(dataA);
}

I would just like feedback on how to fix my error or what I could do instead. As stated this is my first time using google script, my specialty is ASP.net lol not script. Thank you.

Is 'Temperature_Calibration_csv_id' defined anywhere? i.e. var Temperature_Calibration_csv_id = 'hsdojsdajkhadfsad89ojfsadasdfklsad89'; — Spencer Easton
No, that line is not defined anywhere! What value should i assign to it? — Solomon1234567
The ID of the google sheet you are trying to add the data to. — Spencer Easton
Thank you, that is the part I was confused on. I did not know where to locate the ID. It is in the URL of the sheet. — Solomon1234567

Cooper Cooper · Accepted Answer · 2017-06-15T20:43:02

  function loadingCSV() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var sht=ss.getActiveSheet();
  sht.clearContents();
  var data = loadFile();
  var dataA =Utilities.parseCsv(data);
  var rng = sht.getRange(1, 1, dataA.length, dataA[0].length);
  rng.setValues(dataA);
}

function loadFile(filename,folderID)
{
  var filename = (typeof(filename) !== 'undefined')? filename : 'Temperature Calibration.csv';
  var folderID = (typeof(folderID) !== 'undefined')? folderID : '0B8m9xkDP_TJxUUlueHhXOWJMbjg';
  var fldr = DriveApp.getFolderById(folderID);
  var file = fldr.getFilesByName(filename);
  var s = '';
  while(file.hasNext())
  {
    var fi = file.next();
    var target = fi.getName();
    if(target == filename)
    {
      s = fi.getBlob().getDataAsString();
    }
  }
  return s;
}

Okay this will append the files to the active spreadsheet you'll probably have to open the spreadsheet by id and use getSheetByName to get the sheet you want because this spreadsheet probably won't be active all the time when the trigger is running. I assume the the files all end in .csv. I rename them to .old after reading the data so that the program won't read them multiple times.

function appendingCSV() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var sht=ss.getActiveSheet();
  var drng = sht.getDataRange();
  var lastRow = drng.getLastRow();
  var data = loadFiles();
  var dataA =Utilities.parseCsv(data);
  if(dataA.length>0)
  {
    var rng = sht.getRange(lastRow + 1, 1, dataA.length, dataA[0].length);
    rng.setValues(dataA);
  }
  else
  {
    SpreadsheetApp.getUi().alert('No Data Returned from LoadFiles');
  }
}

function loadFiles(folderID)
{
  var folderID = (typeof(folderID) !== 'undefined')? folderID : '0B8m9xkDP_TJxUUlueHhXOWJMbjg';
  var fldr = DriveApp.getFolderById(folderID);
  var files = fldr.getFiles();
  var s='';
  var re = /^.*\.csv$/i;
  while (files.hasNext())
  {
    var file = files.next();
    var filename = file.getName();
    if(filename.match(re))
    {
      s += file.getBlob().getDataAsString() + '\n';
      file.setName(filename.slice(0,-3) + 'old');
    }
  }
  return s;
}

How to write google script to automatically call data from a google folder drive that contains a CSV file into one Google Sheets

1 Answers