将范围从csv文件复制到Google电子表格 [英] Copy a range from a csv file to a Google Spreadsheet

问题描述

我正在构建Google Spreadsheet，出于各种原因(主要是因为与Excel相比，我要比Google更好)，因此需要从CSV文件导入一系列单元格并将其复制到我的Google Spreadsheet中.

I am building a Google Spreadsheet that for various reasons (mostly to do with me being MUCH better with Excel than Google) will need to import a range of cells from a CSV file and copy them into my Google Spreadsheet.

• 我要从中复制的 csv文件称为'WorkFlowDataStore.csv 我想复制单元格A1:Y1001....它存储在同一Google中 驱动器文件夹作为我的电子表格
• 我要复制到的电子表格称为"WorkCalendar"，我想将信息粘贴到"WorkCal"电子表格的单元格A1中.

• The csv file I am copying from is called 'WorkFlowDataStore.csv and I want to copy cells A1:Y1001....it is stored in the same google drive folder as my spreadsheet
• The spreadsheet I am copying to is called 'WorkCalendar', and I want to paste the information into cell A1 on the sheet 'WorkCal'.

我相当有能力使用复制粘贴将一些VBA拼凑在一起，但对Google脚本几乎一无所知.

I am reasonably competent using copy-and-paste to cobble together some VBA, but know next to nothing about Google scripts.

我已经尝试过的东西包括:

Things I have already tried include:

• 如何写入"Google Spreadsheet"从Excel 2003 VBA .....可以完成一半，但是我所看到的只是大量的wingdings风格的gobbledegook
• https://developers.google.com/apps-script/articles/docslist_tutorial ...但这并不是我真正想要的
• 'importData'公式...但这给了我一个#REF错误，告诉我我需要再创建1000列

• How to write to a "Google Spreadsheet" from Excel 2003 VBA ....this half-works, but all I see is a load of wingdings-style gobbledegook
• https://developers.google.com/apps-script/articles/docslist_tutorial ...but this is not really doing what I want
• The 'importData' formula...but this gives me a #REF error telling me I need to create 1000 more columns

您能提供的任何帮助将不胜感激...我很沮丧，因为这需要30秒钟才能在Excel中完成.

Any help you can give would be greatly appreciated...I am frustrated as this would take me 30 seconds to do in Excel.

谢谢

乔恩

推荐答案

最后我自己回答了这个问题.解决方案是下面的代码，该代码从此处改编而成:

I answered this myself in the end. The solution was the code below, which was adapted from here: How to automatically import data from uploaded CSV or XLS file into Google Sheets, and to make sure that the CSV file I was importing was formatted as an MS-DOS CSV file.

function importData() {
var fSource = DriveApp.getFolderById('0B6MhpeZ2qe57WVdrLWNPdXBIT3c'); 
// reports_folder_id = id of folder where csv reports are saved
var fi = fSource.getFilesByName('WorkFlowDataStoreDOS.csv'); 
// latest report file
var ss = SpreadsheetApp.openById('13U6UAMH8r0fQsWwCE6rSMbLlxCNrlcKAmYxD9YKuG1Q'); 
// 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); // see below for CSVToArray function
var newsheet = ss.insertSheet('NEWDATA'); // create a 'NEWDATA' sheet to store imported data
// loop through csv data array and insert (append) as rows into 'NEWDATA' sheet
for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
  newsheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
}

/*
** report data is now in 'NEWDATA' sheet in the spreadsheet - process it as needed,
** then delete 'NEWDATA' sheet using ss.deleteSheet(newsheet)
*/

var centralStore = ss.getSheetByName ("CentralStore");
var newData = ss.getSheetByName ("NEWDATA");
var calRange = newData.getRange("A1:Y5001");
var calPaste = centralStore.getRange("A1");  

calRange.copyTo(calPaste, {contentsOnly: true})

ss.setActiveSheet(ss.getSheetByName("NEWDATA"));
ss.deleteActiveSheet()
ss.setActiveSheet(ss.getSheetByName("CentralStore"));

}
};


// http://www.bennadel.com/blog/1504-Ask-Ben-Parsing-CSV-Strings-With-Javascript-Exec-Regular-Expression-Command.htm
// This will parse a delimited string into an array of
// arrays. The default delimiter is the comma, but this
// can be overriden in the second argument.




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 );
};

这篇关于将范围从csv文件复制到Google电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！