google script import csv from ftp o google drive

0
votes

i'm using a script to import a csv from a link but there is a way to adapt it for import from an ftp link or google drive file? thank's this is my script

function myFunction3() {
  // 1. Set the required columns as the column number.
  const requiredColumns = [1, 5, 20]; // Please set the required columns. These values are from your question.

  // 2. Retrieve CSV data from an URL.  
  const url = "https://www.stanem.it/csv/InnovaCSV.csv";
  const res = UrlFetchApp.fetch(url);

  // 3. Convert CSV data to Spreadsheet.
  const id = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, title: "tempSpreadsheet"}, res.getBlob()).id;

  // 4. Delete the columns except for the required columns.
  const ss = SpreadsheetApp.openById(id);
  const sheet = ss.getSheets()[0];
  const maxColumn = sheet.getMaxColumns();
  const requests = [];  
  for (let i = 1; i <= maxColumn; i++) {
    if (!requiredColumns.includes(i)) {
      requests.push({deleteDimension: {range: {sheetId: sheet.getSheetId(), dimension: "COLUMNS", startIndex: i - 1, endIndex: i}}});
    }
  }
  Sheets.Spreadsheets.batchUpdate({requests: requests.reverse()}, id);

  // 5. Copy the values of modified CSV data to a sheet in the active Spreadsheet.
  const destinationSheetName = "Sheet1";  // Please set the destilnation sheet name in the active Spreadsheet.
  const dstss = SpreadsheetApp.getActiveSpreadsheet();
  const values = Sheets.Spreadsheets.Values.get(id, sheet.getSheetName()).values;
  Sheets.Spreadsheets.Values.update({values: values}, dstss.getId(), destinationSheetName, {valueInputOption: "USER_ENTERED"});
  
  // 6. Remove the temporat Spreadsheet.
  DriveApp.getFileById(id).setTrashed(true);
}
1
csvgoogle-apps-scriptgoogle-sheets

1 Answers

2
votes

Issue and workaround:

Unfortunately, in the current stage, the file cannot be retrieved from FTP using UrlFetchApp. So in your situation, how about retrieving the file from Google Drive? When the CSV file is retrieved from Google Drive, the modified script is as follows.

Modified script:

Please modify your script as follows. In this modified script, it supposes that the CSV file is put in your Google Drive.

// 2. Retrieve CSV data from an URL.  
const url = "https://www.stanem.it/csv/InnovaCSV.csv";
const res = UrlFetchApp.fetch(url);

// 3. Convert CSV data to Spreadsheet.
const id = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, title: "tempSpreadsheet"}, res.getBlob()).id;

// 3. Convert CSV data to Spreadsheet.
const fileId = "### fileId of CSV file on Google Drive ###";  // Please set the file ID of CSV file.
const id = Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, title: "tempSpreadsheet"}, fileId).id;
  • In this case, the CSV file can be converted with Drive.Files.copy.

Reference: