import csv with google apps script

user_17413128 0
Viewed 513

i need help,

i found this code to import csv in google sheets with apps script. it's work find but i'd like to import in another sheet to the same file.

i can't find the code to enter number ( gid ) of my sheet

function myFunction() {
  function importData()
  {
    var ss = SpreadsheetApp.getActive();
    var url = 'https://your-data-source.csv';
    var text = UrlFetchApp.fetch(url).getContentText();
    var csvData = Utilities.parseCsv(text);
    var sheet = ss.getSheetByName('Sheet1');
    for (var i = 0; i < csvData.length; i++) {
      sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    }
  }
  importData();
}

exampel

thanks

google-apps-script google-sheets csv
1 Answers

I believe your goal is as follows.

  • You want to put the CSV data to the specification sheet in the active Spreadsheet using Google Apps Script.
  • From i can't find the code to enter number ( gid ) of my sheet, you want to choose the sheet using the sheet ID (gid).

Modification points:

  • In order to put the values to the specific sheet, you can achieve it by modifying var sheet = ss.getSheetByName('Sheet1'); to var sheet = ss.getSheetByName('importCSV'); from your sample image.
  • If you want to chose the sheet using the sheet ID (gid), you can use the script like var sheet = ss.getSheets().filter(s => s.getSheetId() == gid);.
  • In your script, each row is put to the sheet in a loop. In this case, the process cost will be high. You can modify your for loop as sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);.

When the above points are reflected in your script, it becomes as follows.

Modified script:

In this modified script, the CSV data is put to the specific sheet using the sheet ID (gid).

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var url = 'https://your-data-source.csv';
  var text = UrlFetchApp.fetch(url).getContentText();
  var csvData = Utilities.parseCsv(text);

  var gid = "1234567890";  // Please set your sheet ID (gid).
  var sheet = ss.getSheets().filter(s => s.getSheetId() == gid);
  if (sheet.length == 1) {
    sheet[0].getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  }
}

or, you can also achieve your goal by modifying var sheet = ss.getSheetByName('Sheet1'); to var sheet = ss.getSheetByName('importCSV');. In this modified script, the CSV data is put to the sheet importCSV.

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var url = 'https://your-data-source.csv';
  var text = UrlFetchApp.fetch(url).getContentText();
  var csvData = Utilities.parseCsv(text);

  var sheet = ss.getSheetByName('importCSV');  // Please set the sheet name.
  sheet[0].getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Note:

  • As the additional information, when you want to put the CSV data to a sheet in other Spreadsheet, you can achieve this by modifying from var ss = SpreadsheetApp.getActive(); to var ss = SpreadsheetApp.openById("###Spreadsheet title###");.

References:

user_71086530
Related