Google App Script to imitate File - Import csv file into a sheet using a custom menu item

Question

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

Answer 1

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.

References:

• Custom Menus in Google Workspace
• Dialogs and Sidebars in Google Workspace Documents

Answer 2

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.

Answer 3

Late to this party, but needed similar functionality. In the comments of the accepted solution OP is asking for the ability to display some form of feedback that processing is happening. I've made a small modification to HTML content to allow for this. This is a dead simple example so modify as required but it addresses OP's ask in the comments:

<form>
  <input type="file" name="file" onchange="importCsv(this.parentNode)" accept=".csv,text/csv">
</form>
<p id="progress" style="display:none;">
  <label for="upload" style="font-family: sans-serif;">Uploadling...</label>
  <progress id="upload"></progress>
</p>
<script>
  function importCsv(e) {
    document.getElementById("progress").style.display = "block";
    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>