How to write a script in Google Spreadsheet

If you are new to Google Spreadsheets and you have never written a script before or if you don't know anything about it, then this tutorial will help you to know how you can write a script in a Google Spreadsheet.

Step 1) Open Script Editor:
From "Tools" menu, click on "Script editor..."

Look at the following screenshot:

This will navigate to you to the Script Editor.
Have a look at the screenshot below:

So, now you are ready to write the code in your Google Spreadsheet script editor. You may notice some code already exists on the script, you may delete it and start fresh with your own script.

Now if you want to print "Hello", then make a function in your script with whatever the name you like. I have made the function functionHello(), have a look at the following script:

function functionHello()
{
  Browser.msgBox("Hello");

}

Now, copy the above script in your script editor and try to run the function:

And then go to your spreadsheet. You will see the small pop-up window displaying message "Hello":

Hope this helps you to understand how to open script editor and start writing scripts in Google Spreadsheet.

Send a Google Spreadsheet sheet to an email address daily by setting a trigger

Send a Google Spreadsheet sheet to an email address weekly or daily or hourly or minutely by setting a timer trigger.

You must have been wondering about whether "Is there a way to send a Google Spreadsheet sheet to an email address daily and that too by a script" If yes then how?

Then you would be very happy if you will have a look at the following script which will help you in achieving it.

So, here is the script:

//////////////

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Send Email", functionName: "sendEmail"}];
  ss.addMenu("Scripts", menuEntries);
};

function sendEmail() {

  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  //var email = Session.getUser().getEmail();
  var email = Session.getEffectiveUser();

  var subject = "this is my subject";

  var body = "this is my body :)";

  

  var oauthConfig = UrlFetchApp.addOAuthService("google");

  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");

  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/");

  oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");

  oauthConfig.setConsumerKey("anonymous");

  oauthConfig.setConsumerSecret("anonymous");

  

  var requestData = {"method": "GET", "oAuthServiceName": "google", "oAuthUseToken": "always"};

  var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="

      + ssID + "&gid=0&portrait=true" +"&exportFormat=xls";

    

  var result = UrlFetchApp.fetch(url , requestData);  

  var contents = result.getContent();

  MailApp.sendEmail(email,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

};

///////////////

You have to copy paste this script in the "script editor", which you will find at the menu bar of the spreadsheet.

I have also coded the "open function" such that after you execute this script and then reopen your spreadsheet you will view "Scripts" menu at the menu bar. Have a look at the screenshot below. 

In this "Scripts" you will have the entry "Send Email", clicking on it will send you the copy of that spreadsheet.

And now to set the trigger you have to go to "Script editor" and there you have to click on the trigger icon below the "Publish" tool bar command. Have a look at the screenshot below:

Then click on "Add a new trigger", then in Run select "sendEmail" and in Events click on "Time-driven" and then select at whatever frequency you want to execute this trigger.

I hope this script would have solved your problems of manually emailing the same spreadsheet again and again.

And If you are not much familiar with scripts then check out the following link:

http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive

Google Spreadsheet - How to quickly scroll down to desired row - Option 1

If you are having a huge amount of rows in your spreadsheet, then you must be wondering how to navigate to your desired row immediately, there are various ways to achieve it. You can scroll if down with the help of a mouse, but take lots of time and you can press "Page Down" key, but sometimes if there are rows in thousands then it will also take your time to navigate.

Following is the script which will help you to navigate to your desired "Cell":

function onOpen()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name:"MyFunction", functionName:"myFunction"}];
  sheet.addMenu("Scripts", entries);
};

function myFunction()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getActiveSheet();

  var input = Browser.inputBox("Input", "Input Row No. To Navigate:", Browser.Buttons.OK);
  mysheet.setActiveCell(mysheet.getDataRange().offset(input-1, 0, 1, 1));
};

As soon as you input the row number, you will be navigated to the "Cell" which is at the first Column in that row.

And in "onOpen()" function, I have coded to insert "myFunction" in the "Scripts" menu at the menu bar. I have entered this function in the script so that you don't have to run the script from script editor again and again and so that you can do it directly from the menu bar.

So now you can navigate or scroll to the desired row.

Here is the option to directly navigate to the last row by default:
http://igoogledrive.blogspot.com/2012/08/scroll-down-to-last-row.html

You can also navigate to your last modified cell, here is the script for it:
http://igoogledrive.blogspot.com/2012/08/how-to-get-location-of-last-modified.html 

How to set default sheet in a google spreadsheet

If you are wondering how to set any sheet as your default sheet when you open a Google Spreadsheet in Google Drive, that is whenever you open your spreadsheet should load and display that default sheet, then here is the solution:

If you want to do it without using a script then just drag that sheet to the left most side sheet, for example see the screenshot below:

Here, Sheet2 is on the extreme left most side, so this will be loaded and displayed as the default sheet when you open this spreadsheet.

And now let us achieve the same using scripts. So if you want to open a specific sheet without changing the order of the sheets in the panel the following script will help you to do so:

function onOpen()
{  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = spreadsheet.getSheetByName("Sheet3");
  //here "Sheet3" will be active sheet.
  spreadsheet.setActiveSheet(sheetname);
};

Just add the function below into the script and change "Sheet3" to whatever sheet you want to open and then open your spreadsheet you will notice that by doing so you will make "yoursheetname" open by default.

If you are not familiar with writing scripts and don't know where to start from then have a look at the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

And if you are sending the link to some to open then spreadsheet, then you can also directly send the URL of the sheet with the ending " #gid=2" , here 2 is Id of "Sheet2", So the link will open the spreadsheet directly with the Sheet2 as active sheet regardless of the order of the sheets by which it has been saved.

I hope this will make you aware of different ways to set the default sheet to be opened at the time of opening of the spreadsheet.