automatically export CSV from googlesheet to our server

user_8630376 0
Viewed 5425

We have CSV file which is located on our server, https://example.com/thisFIle.csv. That is the exported data from Google sheet. A third party website is fetching the data from that CSV file every 3 mins.

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

upon searching I learned that there is this google script section in Google sheet. How can I make the process of instead exporting the CSV then upload manually to our server to be automatically using this google script?

google-drive-api google-apps-script
1 Answers

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 spreadsheetID (the alphanumeric id that appear 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 (https://example.com/post 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');
  Logger.log(csvBlob.getDataAsString());

  //make a post request to the server (I didn't test this part)
  var formData = {
      'name': 'Bob Smith',
      'email': 'bob@example.com',
      'file': csvBlob
  };
  var options = {
      'method' : 'post',
      'payload' : formData
  };
  UrlFetchApp.fetch('https://example.com/post', options);
}

[1] https://script.google.com/home

[2] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

[3] https://developers.google.com/apps-script/reference/spreadsheet/sheet

[4] https://developers.google.com/apps-script/reference/utilities/utilities#newBlob(Byte,String,String)

[5] https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app

[6] https://developers.google.com/apps-script/guides/triggers/installable

user_113522210
Related