CODEWITHSUNDEEP
google-apps-script
Robf279
Robf279

Reputation: 21

Importing csv file to google sheets using script

i am faced with the below problem. I am going to be presented with a .csv file that will contain the following fields:

ItemID  Date    Source name Title   URL Created ItemSourceType

However i won't need all of the fields but i will need to import this into a pre-defined google sheets template, which looks like the below:

Date    Writer  Status  Geog/Area/Product   Source  Title   Note

Again not all of the columns will need to be populated, and so the final solution should look like this.

Date    Writer  Status  Geog/Area/Product   Source  Title   Note 
Today() NULL    NULL    Null                Site    Title-(hyperlinked with URL)   Null

i have put together the following code - some of this has been testing and trying to split out a CSV, and i've not yet attempted to add the hyperlinked field.

function addData() {
  var fSource = DriveApp.getFolderById('138ZRbesgDkKHOROm4izD22oaXoanvsyJ'); // reports_folder_id = id of folder where csv reports are saved
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 12;  // First row of data to process
  var numRows = 2;   // Number of rows to process

  var fSource = DriveApp.getFolderById('138ZRbesgDkKHOROm4izD22oaXoanvsyJ'); // reports_folder_id = id of folder where csv reports are saved
  var fi = fSource.getFilesByName('data.csv'); // latest report file
  var ss = SpreadsheetApp.openById('1wBawJzQ3eAhyjCuetAFg7uUUrum6CDImBcVcxaZ9j84'); // data_sheet_id = id of spreadsheet that holds the data to be updated with new report data

  if ( fi.hasNext() ) { // proceed if "report.csv" file exists in the reports folder
    var file = fi.next();
    var csv = file.getBlob().getDataAsString();
    var csvData = CSVToArray(csv); 
    for ( var i=1, lenCsv=csvData.length; i<lenCsv; i++ ) {
      sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    }
  }


  function CSVToArray( strData, strDelimiter ) {
    // Check to see if the delimiter is defined. If not,
    // then default to COMMA.
    strDelimiter = (strDelimiter || ",");

    // Create a regular expression to parse the CSV values.
    var objPattern = new RegExp(
      (
        // Delimiters.
        "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

        // Quoted fields.
        "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

        // Standard fields.
        "([^\"\\" + strDelimiter + "\\r\\n]*))"
      ),
      "gi"
    );

    // Create an array to hold our data. Give the array
    // a default empty first row.
    var arrData = [[]];

    // Create an array to hold our individual pattern
    // matching groups.
    var arrMatches = null;

    // Keep looping over the regular expression matches
    // until we can no longer find a match.
    while (arrMatches = objPattern.exec( strData )){

      // Get the delimiter that was found.
      var strMatchedDelimiter = arrMatches[ 1 ];

      // Check to see if the given delimiter has a length
      // (is not the start of string) and if it matches
      // field delimiter. If id does not, then we know
      // that this delimiter is a row delimiter.
      if (
        strMatchedDelimiter.length &&
        (strMatchedDelimiter != strDelimiter)
        ){

          // Since we have reached a new row of data,
          // add an empty row to our data array.
          arrData.push( [] );

        }

      // Now that we have our delimiter out of the way,
      // let's check to see which kind of value we
      // captured (quoted or unquoted).
      if (arrMatches[ 2 ]){

        // We found a quoted value. When we capture
        // this value, unescape any double quotes.
        var strMatchedValue = arrMatches[ 2 ].replace(
          new RegExp( "\"\"", "g" ),
          "\""
        );

      } else {

        // We found a non-quoted value.
        var strMatchedValue = arrMatches[ 3 ];

      }

      // Now that we have our value string, let's add
      // it to the data array.
      arrData[ arrData.length - 1 ].push( strMatchedValue );
    }

    // Return the parsed data.
    return( arrData );
  };


  // Fetch the range of cells A2:G3
  var dataRange = sheet.getRange(startRow, 1, numRows,8)//sheet.getRange(startRow, 1, numRows, 8)

  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var ItemID = row[0]
    var Date = row[1]
    var SourceName = row[2]
    var Title = row[3]
    var URL = row[4]
    var Created = row[5]
    var ItemSourceType = row[6]
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }

  var correctFormat = ItemID + ", " + Date + ", " + SourceName + ", " + Title + ", " + URL + ", " + Created + ", " + ItemSourceType;


  Logger.log(correctFormat) 
}

If anyone is able to help point me in the right direction it would be greatly appreciated.

The part of this that i am struggling with is using the Array to populate the spreadsheet with the fields in the correct order, I have put the array below.

  function CSVToArray( strData, strDelimiter ) {
// Check to see if the delimiter is defined. If not,
// then default to COMMA.
strDelimiter = (strDelimiter || ",");

// Create a regular expression to parse the CSV values.
var objPattern = new RegExp(
  (
    // Delimiters.
    "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

    // Quoted fields.
    "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

    // Standard fields.
    "([^\"\\" + strDelimiter + "\\r\\n]*))"
  ),
  "gi"
);

// Create an array to hold our data. Give the array
// a default empty first row.
var arrData = [[]];

// Create an array to hold our individual pattern
// matching groups.
var arrMatches = null;

// Keep looping over the regular expression matches
// until we can no longer find a match.
while (arrMatches = objPattern.exec( strData )){

  // Get the delimiter that was found.
  var strMatchedDelimiter = arrMatches[ 1 ];

  // Check to see if the given delimiter has a length
  // (is not the start of string) and if it matches
  // field delimiter. If id does not, then we know
  // that this delimiter is a row delimiter.
  if (
    strMatchedDelimiter.length &&
    (strMatchedDelimiter != strDelimiter)
    ){

      // Since we have reached a new row of data,
      // add an empty row to our data array.
      arrData.push( [] );

    }

  // Now that we have our delimiter out of the way,
  // let's check to see which kind of value we
  // captured (quoted or unquoted).
  if (arrMatches[ 2 ]){

    // We found a quoted value. When we capture
    // this value, unescape any double quotes.
    var strMatchedValue = arrMatches[ 2 ].replace(
      new RegExp( "\"\"", "g" ),
      "\""
    );

  } else {

    // We found a non-quoted value.
    var strMatchedValue = arrMatches[ 3 ];

  }

  // Now that we have our value string, let's add
  // it to the data array.
  arrData[ arrData.length - 1 ].push( strMatchedValue );
}

// Return the parsed data.
return( arrData );

};

Upvotes: 2

Views: 16250

Answers (1)

Francisco Araujo
Francisco Araujo

Reputation: 148

It seems like this site (https://ctrlq.org/code/20279-import-csv-into-google-spreadsheet) does include several ways to import a CSV into a google spreadsheet.

For instance for importing from google drive you can do:

function importCSVFromGoogleDrive() {

  var file = DriveApp.getFilesByName("data.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

}

After that you will need to filter the data you want, but i guess it is easier to do that once you have imported the data from the csv.

Upvotes: 8

Related Questions