Problems printing csv files on the drive to Google Sheet using Google script

Question

I want to import the csv file on my Google drive to Google Sheet, but there is a problem with my Google script.

I'd like to get some help.

It's got a strange value:

This is the xlsx data I need to Import google sheet:

My script:

function ExportRange() {
    var today = new Date();
    var dd = today.getDate();
    var mm = today.getMonth() + 1; //January is 0!
    if (dd < 10) {
        dd = '0' + dd
    }

    if (mm < 10) {
        mm = '0' + mm
    }

    today = mm + '/' + dd;
    Logger.log(today);
    for (var i = 0; i <= 0; i++) {

        var file = DriveApp.getFilesByName(today + '_' + i + '.csv');
        Logger.log("today file count :" + today + '_' + i + '.csv');

        var spreadsheet = SpreadsheetApp.getActive();
        var test;
        var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
        var yourNewSheet = activeSpreadsheet.getSheetByName(today + '_' + i);
        if (yourNewSheet != null) {
            activeSpreadsheet.deleteSheet(yourNewSheet);
        }
        yourNewSheet = activeSpreadsheet.insertSheet();
        yourNewSheet.setName(today + '_' + i);

        var csv = file.next().getBlob().getDataAsString();
        var csvData = Utilities.parseCsv(csv);

        yourNewSheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

    }
}

What exactly do you mean by strange value (sorry, I do not understand Korean)?Can you provide a sample csv file that features this problem? Does it happen with all files? — ziganotschka
@ziganotschka Hi I have registered the xlsx data that I need to bring as a picture . There is data in the file (name, phone number, address, etc.) However, it can be executed, but what is actually fetched only fetches a strange value (PK []). It's got a strange value photo reference — Sungyoon Jung

Calculuswhiz Calculuswhiz · Accepted Answer · 2020-09-29T12:57:47

The string PK.. is the file signature for the .zip file format and its derivatives. One such derivative is the .XLSX format used by Excel, which is really a .zip archive of XML data (among other things).

You likely have an XLSX file, but you're trying to read it as a CSV, which is dumping the raw data into your sheet. You can either convert it to a real CSV using Excel or see if you can open it as an XLSX with your script. From what I've read, the latter seems more involved, so try the easy thing first.

Problems printing csv files on the drive to Google Sheet using Google script

1 Answers