Let’s work through this together—you’ve relied on this method before without issues, but now it’s throwing a Could not parse text error even after setting cell formats to strings. Here are the most likely fixes and explanations:

1. Critical Mistake: Passing an HTTPResponse Object Instead of Raw Text #

Your code has a key oversight here: UrlFetchApp.fetch() returns an HTTPResponse object, not plain text. When you pass this object directly to Utilities.parseCsv(), it can’t process the data structure, which is almost certainly causing your error.

Fix this by extracting the text content from the response first:

function loadDataOfThread() { 
  // Define your spreadsheet URL first to avoid variable shadowing
  const spreadsheetUrl = "YOUR_SPREADSHEET_URL"; 
  var ss = SpreadsheetApp.openByUrl(spreadsheetUrl); 
  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 + "'"; 
  
  // Rename the GViz URL variable to avoid overwriting your spreadsheet URL
  var gvizUrl = "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 csvResponse = UrlFetchApp.fetch(gvizUrl, options); 
  var csvText = csvResponse.getContentText(); // Extract the raw text content
  var f = Utilities.parseCsv(csvText); // Now parse the actual CSV text
  
  var dataArr = []; 
  if (f.length > 0) { 
    for (var i = 0; i < f.length; i++) { 
      dataArr.push(f[i][1]); 
    } 
  } 
}

I also renamed the GViz URL variable to gvizUrl—your original code reused the url variable, which could cause unexpected behavior if your spreadsheet URL wasn’t defined before that line.

2. Date/Time Columns Might Still Return Unparseable Formats #

Even after setting cells to string format, the GViz query might still return date/time values in raw, problematic formats (like timestamp numbers or formatted dates with hidden special characters).

Explicitly convert date columns to strings in your query using the tostring() function to fix this:

// Example: If column B is your date column, modify the query to convert it
var query = "select A, tostring(B), C where A ='" + ID + "'";

This ensures the date value is returned as a plain string that parseCsv can handle reliably.

3. Check for Empty Rows/Columns or Special Characters in Data #

• Empty rows/columns: ws.getLastRow() and ws.getLastColumn() might include empty rows or columns with hidden formatting, leading to extra commas or blank lines in the CSV response. Add a filter to your query to skip empty rows:

  var query = "select * where A ='" + ID + "' AND A is not null";
  

• Special characters: If your data contains commas, quotes, or line breaks (even in string-formatted cells), these can break CSV parsing. Clean the data in your sheet, or double-check that encodeURIComponent is properly escaping your query.

4. Re-Verify OAuth Permissions #

Occasionally, the OAuth token might lose or lack the right permissions to access the sheet’s GViz endpoint. Try re-authorizing the script by running it manually and accepting the permissions prompt again.

Start with the first fix (extracting raw text content)—that’s the most likely cause of your error. The other steps will help you address any lingering formatting or data issues.

内容的提问来源于stack exchange，提问作者Naoa Lee