google sheet script editor import multiple sheets

Question

Objective

I am trying to use the google script editor to import csv files on google drive into my google sheet. Can someone clarify to me what I'm misunderstanding about the script and what I need to tweak to allow me to paste multiple sheet requests into the below? Apologies if this is something straight forward, this is my first time using the script editor, and I'm not familiar with a lot of this.

Issue

At current my code only populates 1 sheets worth of data. as opposed to treating the second part as the next sheet that I want populated, with a different csv sheet ID.

What I've tried

• Changing the variables to be more unique to get the sheet to treat my variables as different, as I thought it was wiping each time.
• Reviewing other online help forums without luck of finding something similar that I can understand.

Current Code

function onOpen(e) {

SpreadsheetApp.getUi()

.createMenu('RefreshData')

.addItem('Run getCSV', 'getCSV')

.addToUi()

}



function getCSV() {

//ENTER YOUR CSV FILE ID IN THE getFileByID in the ""

var file = DriveApp.getFileById("<file_id>");

var csvFile = file.getBlob().getDataAsString();

var csvData = Utilities.parseCsv(csvFile);

//CHANGE SHEET NAME TO WHATEVER YOUR SHEET IS CALLED

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

sheet.clear();

sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

}

function getCSV() {

    //ENTER YOUR CSV FILE ID IN THE getFileByID in the ""

    var file = DriveApp.getFileById("<file_id2>");

    var csvFile = file.getBlob().getDataAsString();

    var csvData = Utilities.parseCsv(csvFile);

    //CHANGE SHEET NAME TO WHATEVER YOUR SHEET IS CALLED

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

    sheet.clear();

    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

    }

Answer 1

Created an additional function getCSV2 which states the sheet name and ID which is then populated and looped in the getCSV function.

function onOpen(e) {

SpreadsheetApp.getUi()

.createMenu('RefreshData')

.addItem('Run getCSV', 'getCSV')

.addToUi()

}

function getCSV() {
  getCSV2("sheet1", "<file_id1>");
  getCSV2("sheet2", "<file_id2>");
}

function getCSV2(name, id) {

var file = DriveApp.getFileById(id);

var csvFile = file.getBlob().getDataAsString();

var csvData = Utilities.parseCsv(csvFile);

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);

sheet.clear();

sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

}

Answer 2

You have getCSV() defined twice and that will cause problems. Function names, like variables, should be unique. Otherwise, they'll get rewritten.

Take the case of two test() functions. Every time you run testAll(), the output will always be 2.

function testAll() {
  test(); // 2.0
  test(); // 2.0
}

function test() {
  Logger.log(1);
}

function test() {
  Logger.log(2);
}

What you could do is modify getCSV() so that it accepts inputs and then create a new function that will pass the data into it. Then call that new function in your menu.

/**
 * Add a custom menu to the spreadsheet.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu("RefreshData")
  .addItem("Get CSVs", "importSheets")
  .addToUi();
}

/**
 * Imports all CSV files.
 */
function getCSVs() {
  // Define the CSV files and where you want them to be saved
  var csvs = [
    { fileId: "FILE_ID_1", sheetName: "Sheet1" },
    { fileId: "FILE_ID_2", sheetName: "Sheet2" }
  ];

  // Import the CSVs
  for (var i = 0; i < csvs.length; i++) {
    getCSV_(csvs[i].fileId, csvs[i].sheetName);
  }
}

/**
 * Get the CSV data from a specified file and import into the desired
 * sheet of the active spreadsheet.
 * @param {String} fileId - The file ID of the CSV file
 * @param {String} sheetName - The name of the destination sheet
 */
function getCSV_(fileId, sheetName) {
  //ENTER YOUR CSV FILE ID IN THE getFileByID
  var file = DriveApp.getFileById(fileId);
  var csvFile = file.getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csvFile);

  //CHANGE SHEET NAME TO WHATEVER YOUR SHEET IS CALLED
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.clear().getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}