1

I am trying to retrieve data by ID. Use the 3rd method in this link: How to speed ​up the search data in sheet

I run the function and err : Could not parse text.

I do not understand why I have used this method so many times and ran well, but this case is faulty.

This is my code:

function loadDataOfThread() {


    var ss = SpreadsheetApp.openByUrl(url);
    var ws = ss.getSheetByName("sheet1");
    var ID = "12345";
    var formatRange = ws.getRange(1, 1, ws.getLastRow() ,ws.getLastColumn()).setNumberFormat("@STRING@");

    var query = "select * where A ='" + ID + "'";
    var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/gviz/tq?gid=" + ws.getSheetId() + "&tqx=out:csv&tq=" + encodeURIComponent(query);
    var options = {
        headers: {
            'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
        }
    };
    var csv = UrlFetchApp.fetch(url, options);
    var f = Utilities.parseCsv(csv); // err this line

    var dataArr = [];

    if (f.length > 0) {
        for (var i = 0; i < f.length; i++) {

            dataArr.push(f[i][1]);

        }
    }

}

I think in a spreadsheet whose data type is the date time column and it make err my function but i have convert to string !!! I do not understand why ?

Naoa Lee
  • 135
  • 2
  • 19
  • The problem must lie in your csv content. Hard to say what exactly the problem is without seeing your data, but log `var csv` before parsing it and see what is different in the content compared to the files that work for you. Maybe this helps: https://stackoverflow.com/questions/43067877/utilities-parsecsv-could-not-parse-text-google-apps-script – ziganotschka Sep 09 '19 at 13:15
  • Thank you for helping me, your knowledge will be very helpful to me. Best regards ! – Naoa Lee Sep 12 '19 at 01:28

1 Answers1

2

How about this answer? Your issue might be able to be removed with "PasteDataRequest" because "PasteDataRequest" is better than parseCsv() as the parser of CSV data. In this answer, I would like to propose a method for using "PasteDataRequest" of Sheets API. Please think of this as just one of several answers. The flow of this method is as follows.

  1. Insert a sheet as a temporal sheet.
  2. Put the CSV data to the inserted sheet using "PasteDataRequest" of Sheets API.
  3. Retrieve values from the temporal sheet.
  4. Delete the temporal sheet.

Modified script:

When your script is modified, please modify as follows.

Before you use this script, please enable Sheets API at Advanced Google services.

From: 
var f = Utilities.parseCsv(csv);
To: 
var temp = ss.insertSheet("temp");
var sheetId = temp.getSheetId();
var resource = {requests: [{pasteData: {data: csv.getContentText(), coordinate: {sheetId: sheetId}, delimiter: ","}}]};
Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
var f = temp.getDataRange().getValues();
ss.deleteSheet(temp);

Note:

  • Of course, I think that the issue can be also removed by modifying csv of var csv = UrlFetchApp.fetch(url, options);. But from your question, I cannot image the values of your issue. So I proposed above method. If you want to use other method, can you provide a sample Spreadsheet for replicating your issue? Of course, please remove your personal information. By this, I would like to think of the issue.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165