Unzipping a csv file and pasting the data into a Google sheet

 This little script is just an example of how to retrieve a zip file (by url), unzip the file, and copy/paste the data to a Google sheet tab overwriting whatever is there.


function fetchData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("derivatives")
  var url = "https://archives.nseindia.com/content/historical/DERIVATIVES/2023/JAN/fo03JAN2023bhav.csv.zip"
  var zipblob = UrlFetchApp.fetch(url).getBlob();
  var unzipblob = Utilities.unzip(zipblob);
  var unzipstr = unzipblob[0].getDataAsString();
  var csv = Utilities.parseCsv(unzipstr);
  sheet.getRange('A7:O').clearContent();
  sheet.getRange(6, 1, csv.length, csv[0].length).setValues(csv);
}

Comments

Post a Comment

Popular posts from this blog

How to recover an old script version

Image
 Many of us undisciplined hacks (read: not professional developers) sometimes find ourselves wondering when we will buckle down and start using Github to store our Apps script source files and versions.  If you sometimes find yourself in the same boat: needing to restore or access an old script version, the first thing you probably do is revert back to the old Apps script editor (IDE), (filling out the form regarding the lack of version history as your reason), and then hoping the version queue goes back far enough for you to recover what you need. Well, today my undisciplined friend, I will show you a way to recover your script files, all the way back to version 1!! Yes, no more switching back to the old code editor. I thought about writing a Script for this, and still might, but for now, I'll show the manual process. Step 1) Open the script editor or get the script ID of the script whose version you want to recover some other way.         --if you already know the version number
Read more

Gmail add-ons: Conditional Homepage Content

There may be times when you want to present different content in your add-on's homepage depending on certain conditions. For example,  I want to present a card to the user when they run my add-on for the first time (I'll call it 'card-A'), but present a different card for each subsequent time the add-on is run ('card-B'). I will need to keep track somehow, so I will use apps-script's property service  to retain the information. (Note: for this example, I will use script property service, but you will probably want to use user property service for each individual user if anyone else will be using the add-on).   function   onHomepage () { //The first thing to do is get the script properties and check the content:        const   scriptProperties  =  PropertiesService . getScriptProperties ();      const   runStatus  =  scriptProperties . getProperty ( 'first_run' ); //After giving it some thought, I don't need to present different cards to the user,
Read more