Automate Data Transfer from CSV to Google Sheets

[Will Gilbreath]

function importCSVToGoogleSheet() {
  
  const folderId = 'ID';
  const targetSpreadsheetId = 'ID';
  const targetSheetName = 'Jobs from PP';

  // Access the folder and files
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType(MimeType.CSV);

  // Access the target sheet
  const targetSheet = SpreadsheetApp.openById(targetSpreadsheetId).getSheetByName(targetSheetName);

  // Clear the sheet before importing new data
  targetSheet.clearContents();
  targetSheet.clearFormats();

  // Loop through all the CSV files
  while (files.hasNext()) {
    const file = files.next();
    const csvContent = file.getBlob().getDataAsString();
    const csvData = Utilities.parseCsv(csvContent, ','); // Parsing CSV content

    // Import the data into the target sheet
    targetSheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  }
}

I am trying to delete the data in my target sheet of a workbook and then paste in the data from a csv file within the google drive. It has to be csv source because the data is imported automatically as a csv. There is only one csv and there will always only be one csv. I believe Google can pick up a comma delimiter for the CSV as I can create a Google Sheet from the CSV file but it comes into the sheet I want to paste into in a mess of randomly organized cells.

[David]

Hi, have you tried specifying the character set when reading the file's string data?

....

const charset = ' UTF-16  ' // find the correct charset for your csv

const csvContent = file.getBlob().getDataAsString(charset)
...