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:
To insert the data in the next available row:
sheet.getRange(sheet.getLastRow()+1, 1, csvData.length, csvData[0].length).setValues(csvData);
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 }