How do I write a script that can let me select and upload a CSV file from local drive in a Google Spreadsheet when clicking on a custom menu ? (I want to replicate via script the File-->Import command but in a new menu being rendered onOpen).
There's a similar question here, but the answer uses a method that is now deprecated. Script import local CSV in Google Spreadsheet
Deprecated answer
function doGet(e) {
var app = UiApp.createApplication().setTitle("Upload CSV to Sheet");
var formContent = app.createVerticalPanel();
formContent.add(app.createFileUpload().setName('thefile'));
formContent.add(app.createSubmitButton('Start Upload'));
var form = app.createFormPanel();
form.add(formContent);
app.add(form);
// return app;
SpreadsheetApp.getActiveSpreadsheet().show(app);// show app
}
function doPost(e) {
// data returned is a blob for FileUpload widget
var fileBlob = e.parameter.thefile;
// parse the data to fill values, a two dimensional array of rows
// Assuming newlines separate rows and commas separate columns, then:
var values = []
var rows = fileBlob.contents.split('\n');
for(var r=0, max_r=rows.length; r<max_r; r)
values.push( rows[r].split(',') ); // rows must have the same number of columns
// Using active sheet here, but you can pull up a sheet in several other ways as well
SpreadsheetApp.getActiveSheet()
.getRange( 1, 1, values.length, values[0].length )
.setValues(values);
}
CodePudding user response：
Instead of uploading it from local, it would be simpler to upload it from Google Drive.
Check-out the below code:
function importCSVFromGoogleDrive() {
var file = DriveApp.getFilesByName("file.csv").next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TEST').activate();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
This copies the data from "file.csv" in your Google Drive to the Sheet named "TEST" in your Spreadsheet.
CodePudding user response：
I believe your goal is as follows.
- You want to upload a CSV data to the active sheet by executing the script from the custom menu of Google Spreadsheet.
In this case, how about the following sample script?
Sample script:
Google Apps Script side: Code.gs
Please copy and paste the following script to the script editor as a script.
function onOpen() {
SpreadsheetApp.getUi().createMenu("sample").addItem("import CSV", "importCsv").addToUi();
}
function importCsv(e){
if (!e) {
SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
return;
}
const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString());
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(sheet.getLastRow() 1, 1, csv.length, csv[0].length).setValues(csv);
}
HTML and Javascript side: index.html
Please copy and paste the following script to the script editor as a HTML.
<form><input type="file" name="file" onchange="importCsv(this.parentNode)" accept=".csv,text/csv"></form>
<script>
function importCsv(e) {
const file = e.file.files[0];
const f = new FileReader();
f.onload = d => google.script.run.withSuccessHandler(google.script.host.close).importCsv([[...new Int8Array(d.target.result)], file.type, file.name]);
f.readAsArrayBuffer(file);
}
</script>
Testing:
When you test the above script, please run onOpen or reopen Spreadsheet. By this, you can see the custom menu. When you select "import CSV" from the custom menu, a dialog is opened. When you select a CSV file from your local PC, the CSV data is imported to the active sheet.
Note:
- This is a simple sample script. So please modify this for your actual situation.