Language
한국어

[GAS] 구분자로 분리된 문서 스프레드시트로 입력

2013.09.17 13:41

suritam9 조회 수:1761

http://collaborative-tools-project.blogspot.kr/2012/05/getting-csv-data-into-google.html



위 사이트에는 CSV라 되어 있는데, 실제 코드는 탭으로 구분자를 주고 있다.


var csvData = CSVToArray(rawData, "t"); // turn into an array


게다가 CSVToArray 함수도 다른 데에서 배껴와야 한다.


풀 소스를 아래에 적어본다.


function encode_utf8( s ){

  //This is the code that "I think" turns the UTF16 LE into standard stuff....

  return unescape( encodeURIComponent( s ) );

}


function get_csv() { 

  var url = 'http://~/csvfile.csv'; // Change this to the URL of your file

  //var response = UrlFetchApp.fetch(url); // 원본코드

  var response = UrlFetchApp.fetch(url, {contentType : 'text/html; charset=utf-8'});// 이렇게 바꿔서 문서가 UTF-8일 때 깨짐을 방지했다.

  // If there's an error in the response code, maybe tell someone

  //MailApp.sendEmail("s.brown@york.ac.uk", "Error with CSV grabber:" + response.getResponseCode() , "Text of message goes here")

  Logger.log( "RESPONSE " + response.getResponseCode()); 

   //var data = encode_utf8(response.getContentText().toString());   //원본 코드

  var data = response.getContentText().toString();   // 이렇게 바꿔서 문서가 UTF-8일 때 깨짐을 방지했다.

  return data //as text  

}



function importFromCSV() {

  // This is the function to which you attach a trigger to run every hour  

  var rawData = get_csv(); // gets the data, makes it nice...

  

  var csvData = CSVToArray(rawData, "t"); // turn into an array

  Logger.log("CSV ITEMS " + csvData.length);

  

  //Write data to first sheet in this spreadsheet

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  //Logger.log(sheet);

  

  ////// From: https://developers.google.com/apps-script/articles/docslist_tutorial

  

  // I think this will write data from the 0th cell. It actually needs a line to select ALL the data and delete it,

  // in case there is less data than the previous import.

  

  for (var i = 0; i < csvData.length; i++) {

    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));

     // i+1 이 행을 결정하고, 1이 컬럼을 결정한다.

     //this might be where you would look at the data and maybe...

    // cell.offset(i,i+2).setBackgroundColor("green"); 

    //Logger.log( "i:" + i + " " + 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 );

}