How to send data to Google Sheets as base64 string using Apps Script

Question

I am new to Apps Script and I am trying to implement where I am taking an input through Google Sheets by using Apps Script. The user gets prompted with a HTML form through which the user uploads a PDF file.
The file is further saved in a google drive and further used for processing the PDF.
One way of importing csv file into Google Sheets is uploading the output to the Google Drive and then importing the CSV to a new sheets. I am able to achieve this.
My question is can I cut the middle-ware of Google Drive and directly give base-64 string as an input maybe by using callback url to the Google Sheets and the output is generated?

I can be wrong, but as far as I can tell the UrlFetchApp does about this thing: developers.google.com/apps-script/reference/url-fetch/… — Yuri Khristich
– Yuri Khristich, Commented Aug 2, 2022 at 9:01
But that takes an api link as far as I have understood. I am talking about making a callback to the apps script containing just the base-64 string. — Lav Sharma
– Lav Sharma, Commented Aug 2, 2022 at 9:22
Not sure if full understand your workflow. You have a csv file and you want to put it on the sheet? You can do in directly from local script (Node.js, Python, etc) via Sheets API without uploading on Drive. What the pdf files has to do with this? — Yuri Khristich
– Yuri Khristich, Commented Aug 2, 2022 at 9:33
Supposing you have a base64 encoded string, could you perhaps do something like Utilities.parseCsv(Utilities.newBlob(Utilities.base64decode(base64String)).getDataAsString()); — johndee31415
– johndee31415, Commented Aug 2, 2022 at 9:38
Getting error Error Exception: Could not parse text.. For the method there is just a little typo it is base64Decode and not base64decode. — Lav Sharma
– Lav Sharma, Commented Aug 2, 2022 at 10:12

Yuri Khristich · Accepted Answer · 2022-08-02 10:43:30Z

1

Something like this?

function myFunction() {
  var csv_string = 'aa,bb,cc\n11,22,33'; // original csv string

  var base64data = Utilities.base64Encode(csv_string); // encoded string
  console.log(base64data);
  
  var decoded = Utilities.base64Decode(base64data); // decoded string
  console.log(decoded);
  
  var string = Utilities.newBlob(decoded).getDataAsString(); // csv string
  console.log({string});

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

Reference: https://developers.google.com/apps-script/reference/utilities/utilities?hl=en#parseCsv(String)

Update

It works fine with your CSV:

function myFunction() {

  var csv_string = `Transaction_Date,Particulars,Cr_Dr,Amount,Balance
01-APR-2017,B/F,,,"3,99,068.72"
03-Apr-2017,NEFT-MAHENDRA HARDWARE,Cr,"1,31,427.00","2,67,641.72"
05-Apr-2017,NEFT-INTER DECOR,Cr,"1,01,607.00","1,66,034.72"
07-Apr-2017,INSPECTION CHARGES,Dr,721.00,"1,66,755.72"
10-Apr-2017,BY CLG--            5484,Cr,"90,115.00","76,640.72"
11-Apr-2017,INDORE MAIN 387 GOVT,Dr,299.00,"76,939.72"
11-Apr-2017,INDORE MAIN 387 GOVT,Dr,"42,278.00","1,19,217.72"
12-Apr-2017,BY CLG-259-           50754,Cr,"68,842.00","50,375.72"
12-Apr-2017,BY CLG-COB-          251639,Cr,"30,000.00","20,375.72"
12-Apr-2017,DILIP AJMERA  S/O JA,Dr,"2,89,868.00","3,10,243.72"`;

  var base64data = Utilities.base64Encode(csv_string); // encoded string
  
  var decoded = Utilities.base64Decode(base64data); // decoded string
  
  var string = Utilities.newBlob(decoded).getDataAsString(); // csv string

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

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

Here is my sheet.

edited Aug 2, 2022 at 10:43

answered Aug 2, 2022 at 10:13

Yuri Khristich

14.6k3 gold badges11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lav Sharma Over a year ago

having issue while parsing csv, facing the error Error Exception: Could not parse text. as my sample csv looks like this

Yuri Khristich Over a year ago

Looks like there is a problem with how you get the CVS. It doesn't look well at some places. It contains a number of uncommon symbols like ^, etc. But when I copy it from your Google Doc directly into the script the native Google csv parser get me no errors as you can see. All the 3000+ rows are parsed fine.

Yuri Khristich Over a year ago

And one more thing. Your CSV is quite big. Over 3000 rows. It takes about 200 kilobytes as txt. Make sure that it doesn't exceed the limitation on the size of a string.

Lav Sharma Over a year ago

Thankyou for the detailed answer it worked, I will take the size into consideration.

Collectives™ on Stack Overflow

How to send data to Google Sheets as base64 string using Apps Script

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related