Skip to content
Advertisement

How can I stop this from over writing the data and adding it to last row in google sheets

I am trying to import data from a csv file, the script works as it brings the data in but over wright the data already in my sheet, please how do I get this script to insert in the next available row also how do I only fetch from row 3?

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
  var csvMenuEntries = [{name: "Load from CSV file", functionName: "importFromCSV"}];
  ss.addMenu("Search Google Drive", searchMenuEntries);
  ss.addMenu("CSV", csvMenuEntries);
}

function importFromCSV() {
  var fileName = Browser.inputBox("Enter the name of the file in your Google Drive to import (e.g. myFile.csv):");
   
  var searchTerm = "title = '"+fileName+"'";

  var files = DriveApp.searchFiles(searchTerm)
  var csvFile = "";
   

  while (files.hasNext()) {
    var file = files.next();
    if (file.getName() == fileName) {
      csvFile = file.getBlob().getDataAsString();
      break;
    }
  }

  var csvData = Utilities.parseCsv(csvFile);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData); 
}

Advertisement

Answer

Explanation:

  1. To insert the data in the next available row:

    sheet.getRange(sheet.getLastRow()+1, 1, csvData.length, csvData[0].length).setValues(csvData);
    
  2. You can use slice to get from the third row onwards:

    csvData.slice(2);
    

Solution:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
  var csvMenuEntries = [{name: "Load from CSV file", functionName: "importFromCSV"}];
  ss.addMenu("Search Google Drive", searchMenuEntries);
  ss.addMenu("CSV", csvMenuEntries);
}

function importFromCSV() {
  var fileName = Browser.inputBox("Enter the name of the file in your Google Drive to import (e.g. myFile.csv):");
   
  var searchTerm = "title = '"+fileName+"'";

  var files = DriveApp.searchFiles(searchTerm)
  var csvFile = "";
   

  while (files.hasNext()) {
    var file = files.next();
    if (file.getName() == fileName) {
      csvFile = file.getBlob().getDataAsString();
      break;
    }
  }

  var csvData = Utilities.parseCsv(csvFile);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  var s_csvData = csvData.slice(2); // <- new code
  
  sheet.getRange(sheet.getLastRow()+1, 1, s_csvData.length, s_csvData[0].length).setValues(s_csvData); // <- modification
}
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement