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, https://example.com/thisFIle.csv. 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 (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
Rate this:
- October 19, 2022
- Auto export and upload, Google Sheet
