Google Spreadsheet automate Cell Clear and add Cell Value on Open event

This is a continuation of my post http://itskhembot.blogspot.com/2018/07/populate-google-spreadsheet-cells-with.html , since I want it to update cell API data value on Open (or on hour basis, which you can set on triggers)

I know it will be useful too on other circumstances.

If you've followed my previous post you'll know where to add the script in Tools > Script Editor

Now this time we will add new scripts, first one would be the clearSheet function:

function cleanSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('A:C').clearContent();
}

I'm clearing A to C columns only since I only have data on those columns, you can put your desired range on it. Append the above code to our existing ImportJSON script

Save. Now we will add triggers

On the same window, click Edit > All your triggers

It will popup a window for the triggers, click add if you have no triggers yet

Next set trigger with event From Spreadsheet and On Open event

It should look like this. Click Save
Now reopen spreadsheet and the trigger will function, also you can have it run by time or by calendar.
For me I will be adding a cell value function to retrigger the API import script
I'll be adding the call function for the API import below the cleanSheet function

function loadAPI(){
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1,1).setFormula('=ImportJSON("https://api.github.com/users/hadley/repos", "/id,/fullname,/owner/html_url,/owner/avatar_url", "noInherit, noTruncate")');
}

It should look like this

Then add the trigger for it, and it should look like this

Now it will repopulate data on open, if there are new records in the API it will add it to the spreadsheet. Convenient!

Also you may need to have permissions for the triggers, so make sure you allow it on your account.
Enjoy! ;)