• I am trying to import base-64 CSV into Google Apps Script.
• I am facing the below error

Error   
Exception: This action would increase the number of cells in the workbook above the limit of 10000000 cells.
myFunction  @ Code.gs:16

• Followed this, but not coming to a solution.
• Code new-base-64-string

function myFunction() {

  var csv_string = [base-64-string];
  
  var decoded = Utilities.base64Decode(csv_string); // decoded string
  
  var string = Utilities.newBlob(decoded).getDataAsString(); // csv string

  var array = Utilities.parseCsv(string) // 2d array

  console.log(array.length);
  console.log(array[0].length);

  Logger.log(array.every(function(row){return row.length === 1}));

  SpreadsheetApp.getActiveSheet().getRange(1,1,array.length,array[0].length).setValues(array);
}

• Log for console.log(array.length); 1
• Log for console.log(array[0].length); 12537
• Log for Logger.log(array.every(function(row){return row.length === 1})); false

CSV Link

CodePudding user response：

When I saw the data decoded from your base64, it seems that the data is JSON data which is not CSV data. I think that the reason for your current issue is due to this.

From your provided data, in order to parse the data, how about the following sample script?

Sample script:

function myFunction() {
  var base64 = "###"; // Your provided base64 data.
  
  var decoded = Utilities.base64Decode(base64);
  var obj = JSON.parse(Utilities.newBlob(decoded).getDataAsString());
  var obj2 = JSON.parse(obj.Rows);
  var actualHeader = ["Transaction Date","Particulars","Amount","Cr/Dr","Balance","Transaction Type","Party Name","Charge Name","Charge Class"];
  var objHeader = ["Transaction_Date","Particulars","Amount","Cr_Dr","Balance","Transaction_Type","Normalized_Party_Name_Label","Normalized_Charge_Name_Label","Charge_Class"];
  var array = [actualHeader, ...obj2.map(o => objHeader.map(h => o[h] || ""))];
  
  // Here, the value is put to the active sheet.
  SpreadsheetApp.getActiveSheet().getRange(1,1,array.length,array[0].length).setValues(array);
}

• actualHeader is from the data of your comment. objHeader is from the JSON data.

• If your provided JSON data of https://drive.google.com/file/d/1I9Liq1ePrqZXQlejMwpOu3q3VZo_7hCI/view?usp=sharing is used, var base64 can be modified as follows.

    var data = DriveApp.getFileById("1I9Liq1ePrqZXQlejMwpOu3q3VZo_7hCI").getBlob().getDataAsString();
    var base64 = JSON.parse(data).Output.Augmented_Transactions.Base64EncodedFile;
  

• When this script is run, the base64 data is decoded and parse it as JSON data. And, create an array. And then, the array is put to the active sheet.

• If you want to convert array to CSV data, how about using var csv = array.map(r => r.join(",")).join("\n")?

Reference:

• map()