Stuck on this Problem?

Automatically export CSV from googlesheet to our server

Viewed 6k times on stackoverflow

The situation:

We have a CSV file that is located on our server, That is the exported data from Google Sheets. A third-party website fetches data from that CSV file every 3 mins.

The company that I am working for is in the booking business, so their agents are the persons updating the Google sheet, then I will manually export the data into CSV then upload it to our server.

Upon searching, I learned about this google script section in Google Sheets. So, the question I asked was “How can I make the process of instead exporting the CSV then upload manually to our server to be automatically using this google script?”

The solution:

Yes, you can achieve that process with Google App Script [1].

Using the methods of the classes in the spreadsheet service [2] and [3], you can obtain the data of the sheet you want having the spreadsheet (the alphanumeric id that appears inside the URL when you open a Google Sheet) . Then loop through the data and parse it to a string in CSV format.

With the csv string you can create a blob object [4] that will be sent to the server through a post request using the fetch method [5].

To make your code runs automatically you can use the manual triggers for example, and set them to run every minute or as you need [6].

You have to set your server application to receive the post request and set the request URL in the App Script ( as an example). Below is the code I tested until obtain the csvBlob variable:

					function myFunction() {
  var ss = SpreadsheetApp.openById("SpreadsheetID");
  var sheet = ss.getSheets()[0];

  // This represents ALL the data
  var range = sheet.getDataRange();
  var values = range.getValues();
  var csvStr = "";
   // This creates a string of the spreadsheet in CSV format with a trailing comma
   for (var i = 0; i < values.length; i++) {
       var row = "";
       for (var j = 0; j < values[i].length; j++) {
           if (values[i][j]) {
              row = row + values[i][j];
           row = row + ",";
       row = row.substring(0, (row.length-1));
       csvStr += row + "\n";
  //creates de Blob of the csv file
  var csvBlob = Utilities.newBlob(csvStr, 'text/csv', 'example.csv');

  //make a post request to the server (I didn't test this part)
  var formData = {
      'name': 'Bob Smith',
      'email': '',
      'file': csvBlob
  var options = {
      'method' : 'post',
      'payload' : formData
  UrlFetchApp.fetch('', options);
Share on facebook
Share on twitter
Share on linkedin

Rate this:

5/5 - (1 vote)

Related Post