Google Drive Upload Csv as Google Sheet

How to import CSV files into Google Sheets using Apps Script

In this tutorial, I'll show you how to use Apps Script to automatically import CSV files into Google Sheets from a URL or from Google Drive.

Comma-Separated Values (CSV) files are a very common file format for storing tabular data in the concern world. For case, a salesperson might export a list of leads from their CRM system every bit a CSV file and so import this list into a Marketing Automation product to ship emails. The CSV file format provides a convenient way to transfer tabular data from i application to another.

A mutual way to import CSV data into Google Sheets is past using the Import feature from the File menu. While this method works very well, this is a manual process and tin be time consuming if your work involves importing lots of CSV files everyday. This tutorial volition show you how to automate the process of importing CSV files by using Apps Script.

Prerequisites

This tutorial assumes that you're familiar with:

• Google Sheets

• Bones coding concepts (If you're new to coding, please bank check out a series of tutorials that I've written on learning to code using Google Sheets and Apps Script.).

• Creating and running Apps Scripts.

• Authenticating and Authorizing Apps Scripts.

• Prompting the user for input using Apps Script.

Iv steps to build an awarding that will let you import CSV files from a URL or from Google Drive

• Step ane — Create a new Google Sheets spreadsheet or open an existing spreadsheet

• Step 2 — Create a custom menu that volition let users cull where to import the CSV file from: Google Drive or from a URL

• Step 3 — Prompt the user for the URL or for the Google Drive Filename

• Step 4 — Import the CSV file into a new sheet in the spreadsheet

Step ane — Create a new Google Sheets spreadsheet or open an existing spreadsheet

Create a new spreadsheet or open an existing spreadsheet where you desire to import CSV files.

Step 2 — Create a custom menu that volition let users choose where to import the CSV file from: Google Drive or from a URL

Open the script editor by selecting Tools —> Script editor. Replace the existing lawmaking in the editor with the lawmaking below.

//@OnlyCurrentDoc function onOpen(e) {   var ui = SpreadsheetApp.getUi();   ui.createMenu("Import CSV information 👉️")     .addItem("Import from URL", "importCSVFromUrl")     .addItem("Import from Drive", "importCSVFromDrive")     .addToUi(); }  //Displays an alert as a Toast message function displayToastAlert(message) {   SpreadsheetApp.getActive().toast(message, "⚠️ Alarm");  }  //Placeholder office to import CSV files from a URL function importCSVFromUrl() {   displayToastAlert("Import from URL.");  }  //Placeholder function to import CSV files from Google Drive role importCSVFromDrive() {   displayToastAlert("Import from Drive.");  }

The displayToastAlert() function displays notifications to the user. The code has 2 placeholder functions (importCSVFromUrl() and importCSVFromDrive()) that we will be working on in the rest of this tutorial.

Run the lawmaking by clicking the play icon. Once the script runs, you should run across the "Import CSV information 👉" custom menu in your spreadsheet.

Try selecting an detail from this bill of fare.

When you select "Import from URL", you should see a notification in the lower right corner of the spreadsheet.

Footstep three — Prompt the user for the URL or Google Drive Filename of the CSV file

Next, nosotros demand to implement functionality to ask the user to specify which URL or file to import the CSV data from.

Create a new function called prompUserForInput() to prompt the user for input. It will accept the text to display in the prompt every bit a parameter and it will render the value entered past the user.

function promptUserForInput(promptText) {   var ui = SpreadsheetApp.getUi();   var prompt = ui.prompt(promptText);   var response = prompt.getResponseText();   return response; }

Alter the functions importCSVFromUrl() and importCSVFromDrive() to utilize the prompUserForInput() function to go the URL or Filename from the user. And so, include this information in the alarm bulletin to help us verify that the user'due south input was received successfully.

function importCSVFromUrl() {   var url = promptUserForInput("Please enter the URL of the CSV file:");   displayToastAlert("Importing from URL: " + url);  }  function importCSVFromDrive() {   var fileName = promptUserForInput("Please enter the proper noun of the CSV file to import from Google Drive:");   displayToastAlert("Importing from Drive: " + fileName);  }

Test your changes past selecting "Import from Bulldoze" from the menu.

You lot should see a prompt asking you to enter the name of the file to import.

Enter the proper name of some CSV file and select [Ok]. The file doesn't take to exist in Google Drive (at least non yet), we're just testing to see if we can successfully receive input from the user.

You lot should run across the filename you entered displayed in the alert message.

Step 4 — Import the CSV file into a new canvass in spreadsheet

The adjacent step is to write the code to actually import the contents of the CSV file into a new canvas in the spreadsheet.

Here is how our code volition work:

• First, nosotros will write the code to access the CSV file from a URL or from Google Drive and load its contents into a variable as a cord.

• And so, we volition convert the cord into a 2nd assortment whose rows and columns are populated with the data in the CSV file.

• Finally, we volition insert a new canvas in the Google Sheets spreadsheet and write this 2D assortment into it.

• Display an alert message letting the user know that the CSV file has been imported successfully.

Pace 4.1 — Write the lawmaking to access the CSV file from a URL or Google Drive and load its contents into a variable equally a string

Step iv.i.ane — Accessing CSV files from a URL

• Go the URL of the CSV file from the user.

• Fetch its contents by using the UrlFetchApp.fetch() method.

• Display the contents using the displayToastAlert() office so you can see if it worked.

office importCSVFromUrl() {   var url = promptUserForInput("Please enter the URL of the CSV file:");   var contents = UrlFetchApp.fetch(url);   displayToastAlert(contents); }

Save your script, open your Google Canvas and select the "Import from URL" item from the menu and enter the URL of a CSV file.

I tested the script using the CSV file containing a listing of country codes and names at this URL: https://raw.githubusercontent.com/openmundi/earth.csv/main/countries(204)_olympics.csv

When the script finishes running, you should meet the contents of the CSV file displayed in the Toast notification message.

Step 4.1.2 — Accessing CSV files from Google Drive

• Get the name of the CSV file from the user.

• Search Google Drive to find files with this name.

• If the file is non found, brandish an error message to the user to let them know that the file was not found.

• If multiple files are found, brandish an error message to the user to let them know that the script does non yet support letting them choose the right file to import.

• Fetch the file's contents and brandish it using the displayToastAlert() function and so you tin can come across if information technology worked.

function importCSVFromDrive() {   var fileName = promptUserForInput("Please enter the name of the CSV file to import from Google Drive:");   var files = findFilesInDrive(fileName);   if(files.length === 0) {     displayToastAlert("No files with name \"" + fileName + "\" were constitute in Google Drive.");     render;   } else if(files.length > 1) {     displayToastAlert("Multiple files with name " + fileName +" were found. This program does not support picking the right file even so.");     return;   }   var file = files[0];   var contents = file.getBlob().getDataAsString();   displayToastAlert(contents); }  //Returns files in Google Drive that have a certain proper name. function findFilesInDrive(filename) {   var files = DriveApp.getFilesByName(filename);   var result = [];   while(files.hasNext())     outcome.push(files.side by side());   return event; }

Let's say the CSV file in Google Drive is students.csv and it has the post-obit contents:

id,firstName,lastName one,Robert,Bayer 2,Nathalia,Liu 3,Jane,Crossman 4,Abigail,Lev 5,Bridgette,Morrison 6,Jack,Roberts 7,Sam,Connors 8,Paige,Thompson nine,Penny,Lane ten,Jack,Forrester

To exam the code, select "Import from Google Drive"

Enter the proper noun of the CSV file (students.csv in my instance) and select [Ok].

The script will run and when it finishes running, you lot should see the CSV file'southward contents displayed in the toast message.

So far, our script can access CSV files from a URL or from Google Drive and access its contents. The side by side footstep is to convert this data into a 2d assortment (i.e., into rows and columns) so the data tin exist written to the spreadsheet.

Stride four.3 — Catechumen the string into two-dimensional (2D) assortment

In society to write the CSV data into the spreadsheet, we demand to convert the string to a 2-dimensional array. That is, nosotros need to parse the string and convert its contents into tabular course.

The method parseCsv() in the Apps Script'south Utilities library converts a string containing CSV information into a 2-dimensional array.

Utilities.parseCsv(information);

The table below shows an example of the two-dimensional array data construction returned past the parseCsv() method.

String containing CSV data	2D assortment representation of CSV data
id,firstName,lastName 1,Robert,Bayer 2,Nathalia,Liu	[ ["id","firstName","lastName"], ["1","Robert", "Bayer"], ["2","Nathalia","Liu"] ]

Pace 4.three.i — Use the parseCsv() method in the importCSVFromDrive() role

role importCSVFromDrive() {   var fileName = promptUserForInput("Please enter the name of the CSV file to import from Google Drive:");   var files = findFilesInDrive(fileName);   if(files.length === 0) {     displayToastAlert("No files with proper name \"" + fileName + "\" were found in Google Bulldoze.");     return;   } else if(files.length > 1) {     displayToastAlert("Multiple files with name " + fileName +" were found. This plan does not support picking the right file yet.");     return;   }   var file = files[0];   var contents = Utilities.parseCsv(file.getBlob().getDataAsString());   displayToastAlert(contents); }

Stride four.3.2 — Apply the parseCsv() method in the importCSVFromUrl() function

part importCSVFromUrl() {   var url = promptUserForInput("Please enter the URL of the CSV file:");   var contents = Utilities.parseCsv(UrlFetchApp.fetch(url));   displayToastAlert(contents); }

Pace 4.iv — Insert a new canvass, write the 2D array into it and allow the user know that the CSV file has been imported successfully

The final pace is to write the 2D array containing rows and columns from the CSV file to the spreadsheet. We'll create a new function called writeDataToSheet() that accepts the 2D array as input and writes it to a new sheet in the spreadsheet.

function writeDataToSheet(data) {   var ss = SpreadsheetApp.getActive();   canvas = ss.insertSheet();   canvas.getRange(i, 1, information.length, data[0].length).setValues(data);   return sheet.getName(); }

Stride 4.four.1 — Write CSV data to the sheet from the importCSVFromDrive() office

Alter the importCSVFromDrive() part to write the 2d array in the variable contents to the spreadsheet using the writeDataToSheet() function. Then, alter the notification message to let the user know that the CSV file has been imported successfully.

function importCSVFromDrive() {   var fileName = promptUserForInput("Please enter the proper noun of the CSV file to import from Google Drive:");   var files = findFilesInDrive(fileName);   if(files.length === 0) {     displayToastAlert("No files with name \"" + fileName + "\" were found in Google Drive.");     return;   } else if(files.length > 1) {     displayToastAlert("Multiple files with proper name " + fileName +" were found. This plan does not support picking the correct file yet.");     return;   }   var file = files[0];   var contents = Utilities.parseCsv(file.getBlob().getDataAsString());   var sheetName = writeDataToSheet(contents);   displayToastAlert("The CSV file was successfully imported into " + sheetName + "."); }

Now try importing a CSV file from Google Drive. Your script will insert a new sheet in your spreadsheet with the contents of the CSV file.

Step 4.4.2 — Write CSV data to the sail from the importCSVFromUrl() function

Alter the importCSVFromUrl() function to write the 2nd assortment in the variable contents to the spreadsheet using the writeDataToSheet() function. Then, modify the notification message to permit the user know that the CSV file has been imported successfully.

function importCSVFromUrl() {   var url = promptUserForInput("Please enter the URL of the CSV file:");   var contents = Utilities.parseCsv(UrlFetchApp.fetch(url));   var sheetName = writeDataToSheet(contents);   displayToastAlert("The CSV file was successfully imported into " + sheetName + "."); }

Now effort importing a CSV file from a URL. Your script will insert a new canvass in your spreadsheet with the contents of the CSV file.

That's it! You lot've successfully written a script to import CSV files from a URL or from Google Bulldoze.

The total script to import CSV files into Google Sheets using Apps Script

If you lot've been following the steps in this tutorial, your final script should expect similar this:

//@OnlyCurrentDoc office onOpen(e) {   var ui = SpreadsheetApp.getUi();   ui.createMenu("Import CSV data 👉️")     .addItem("Import from URL", "importCSVFromUrl")     .addItem("Import from Drive", "importCSVFromDrive")     .addToUi(); }  //Displays an alert as a Toast message function displayToastAlert(message) {   SpreadsheetApp.getActive().toast(message, "⚠️ Alert");  }  //Imports a CSV file at a URL into the Google Sheet office importCSVFromUrl() {   var url = promptUserForInput("Please enter the URL of the CSV file:");   var contents = Utilities.parseCsv(UrlFetchApp.fetch(url));   var sheetName = writeDataToSheet(contents);   displayToastAlert("The CSV file was successfully imported into " + sheetName + "."); }   //Imports a CSV file in Google Drive into the Google Sheet function importCSVFromDrive() {   var fileName = promptUserForInput("Please enter the name of the CSV file to import from Google Bulldoze:");   var files = findFilesInDrive(fileName);   if(files.length === 0) {     displayToastAlert("No files with proper noun \"" + fileName + "\" were plant in Google Bulldoze.");     return;   } else if(files.length > 1) {     displayToastAlert("Multiple files with name " + fileName +" were establish. This program does non back up picking the correct file yet.");     render;   }   var file = files[0];   var contents = Utilities.parseCsv(file.getBlob().getDataAsString());   var sheetName = writeDataToSheet(contents);   displayToastAlert("The CSV file was successfully imported into " + sheetName + "."); }  //Prompts the user for input and returns their response function promptUserForInput(promptText) {   var ui = SpreadsheetApp.getUi();   var prompt = ui.prompt(promptText);   var response = prompt.getResponseText();   render response; }  //Returns files in Google Drive that accept a certain proper noun. function findFilesInDrive(filename) {   var files = DriveApp.getFilesByName(filename);   var result = [];   while(files.hasNext())     result.push(files.adjacent());   render result; }  //Inserts a new sheet and writes a 2d array of information in it role writeDataToSheet(data) {   var ss = SpreadsheetApp.getActive();   canvas = ss.insertSheet();   sheet.getRange(1, 1, data.length, data[0].length).setValues(data);   return sheet.getName(); }

Ideas to extend the script further

Hither are some enhancements that you can consider making to the script:

• Make your script run periodically by using a fourth dimension-driven trigger. Depending on your use instance, you lot might want to modify the writeDataToSheet() part to continue appending data to the same sheet versus creating new sheets.

• Add an option to import CSV files from Gmail.

• Automate your workflow even further by importing the CSV file, performing some analysis automatically and and so emailing a report to your dominate or your squad.

If you'd like me to write a tutorial on any of the above topics (or some other topic), please let me know using the feedback form at the bottom of this post.

Conclusion

In this tutorial, you learned how to automatically import CSV files into a Google Canvass using Apps Script.

Stay up to date

Follow me via electronic mail to receive actionable tips and other exclusive content. I'll also send you notifications when I publish new content.

By signing up you agree to the Privacy Policy & Terms.

---

Have feedback for me?

I'd capeesh whatever feedback you tin can requite me regarding this mail service.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is too welcome. Thank you so much!

ernestdientiong.blogspot.com

Source: https://spreadsheet.dev/how-to-import-csv-files-into-google-sheets-using-apps-script

Share this post