CODEWITHSUNDEEP
csvgoogle-apps-scriptgoogle-sheetsgoogle-apps
Mahad Khan
Mahad Khan

Reputation: 57

Append new CSV data to Google Sheet

How can I append imported CSV data to an existing Google Spreadsheet? Once imported there is an empty row at the bottom, and every subsequent time I run the code I would like it to insert the new CSV data following that empty end row.

Here is my existing code: 

function getCSV() {
var fSource = DriveApp.getFolderById('0B2lVvlNIDosoajRRMUwySVBPNVE'); //       reports_folder_id = id of folder where csv reports are saved
var fi = fSource.getFilesByName('L661_BOM-CAD_14-12-15.csv'); // latest       report file
var ss    =SpreadsheetApp.openById('1WEDYfEudYsbkUhHbCxZspEbNXz3cjQIe3JdhnbFmmYA'); //      data_sheet_id = id of spreadsheet that holds the data to be updated with new      report data
var sd = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSheet();
var target = new Array()
var sSheet = SpreadsheetApp.getActiveSpreadsheet();
sheet = SpreadsheetApp.setActiveSheet(sSheet.getSheets()[0]);

if ( fi.hasNext()) {
var file = fi.next();
var csv = file.getBlob().getDataAsString();
var csvData = CSVToArray(csv); // see below for CSVToArray function

var sheet = ss.getSheets()[0];

s.getRange(+1, 1, csvData.length, csvData[5].length).setValues(csvData);

}
};


function CSVToArray( strData, strDelimiter ){

strDelimiter = (strDelimiter || ";");

   var objPattern = new RegExp(
    (
        // Delimiters.
        "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

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

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

var arrData = [[]];

var arrMatches = null;

while (arrMatches = objPattern.exec( strData )){


    var strMatchedDelimiter = arrMatches[ 1 ];
    if (
        strMatchedDelimiter.length &&
        strMatchedDelimiter !== strDelimiter
        ){

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

    }

    var strMatchedValue;
    if (arrMatches[ 2 ]){
        strMatchedValue = arrMatches[ 2 ].replace(
            new RegExp( "\"\"", "g" ),
            "\""
            );

    } else {

        strMatchedValue = arrMatches[ 3 ];
    }

    arrData[ arrData.length - 1 ].push( strMatchedValue );
}
return( arrData );
  }

Upvotes: 3

Views: 3022

Answers (2)

jbjon
jbjon

Reputation: 1257

This can now be simplified using the parseCSV utility. If you pass in the values for your fSource, fi, and ss variables to this amended function it should still work and save having to use your CSVToArray method. Thanks for the point in the right direction. By default your spreadsheet should load to the first sheet (0) already so the additional 'getActiveSpreadsheet' & getActiveSheets are redundant I think.

function getCSVAndAppend(spreadsheetId, folderId, filename) {
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByName(filename);

  var openSpreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var activeSheet = SpreadsheetApp.getActiveSheet();

  if ( files.hasNext()) {
    var file = files.next();
    var csv = file.getBlob().getDataAsString();
    var csvData = Utilities.parseCsv(csv);

    var lastrow = activeSheet.getLastRow();
    activeSheet.getRange(lastrow + 1, 1, csvData.length, csvData[0].length).setValues(csvData);
  }
}

Upvotes: 3

Mahad Khan
Mahad Khan

Reputation: 57

I managed to fix the issue by adding getLastRow(); command

hopefully this helps someone else out

var lastrow = s.getLastRow();
s.getRange(lastrow + 1, 1, csvData.length, csvData[0].length).setValues(csvData);

Upvotes: 1

Related Questions