How to condition a cell, so that if the value of a cell is higher than a certain number, it will automatically show a certain number or else show that value.

How to condition a cell, so that if the value of a cell is higher than a certain number, it will automatically show a certain number or else show that value.

Here is the script to have this condition on a particular Cell:

function onEdit(e) {

 myFunction();

}

function myFunction()

{

  

  var myNumber = 10; // Set your number which will be used to compare.

  

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  var sheet = ss.getSheetByName("Sheet11"); // your sheet name

  var range = sheet.getDataRange();

  var CellValue = range.offset(0, 0, 1, 1).getValue(); // Set your Cell position

  

  if( CellValue > myNumber )

  {

    range.offset(0, 0, 1, 1).setValue(myNumber);  

  }

}

Function onEdit(e) will automatically be called everytime you make changes on your sheet.

Note:

You have to set the position of your Cell in Function: "range.offset". First argument is for Row number, second is for Column number, third is for No. of rows (here let it be one because we want to get or set value of only one Cell), and fourth is for No. of columns (let this will also be 1).

So for A1 Cell we will have range.offset(0,0,1,1)

And for A2 we will have range.offset(1,0,1,1)

I hope this will help you having the condition on a particular cell.

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.

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

How to use importRange with dynamic sheet name

How to use importRange with dynamic sheet name

Question:

How to use importRange with dynamic sheet name

Answer:

I have two spreadsheets, let's call them "SpreadsheetA" and " SpreadsheetB". 

"SpreadsheetA" has several sheets inserted. Now from within "SpreadsheetB" I would like to get the data from cell B4 of "SpreadsheetA", but depending on the sheet-name I insert in a cell (A1 in this example) within "SpreadsheetB".

I have inserted the following formula in Cell B3 in "SpreadsheetB" to get the desired results:

=ImportRange("0AmMTqpzD9YRndFRjTDBBc3VxTlFub0pTLUVKWkVIdVE";CONCATENATE(A1;"!B4"))

Formula "ImportRange" has two arguements first one is key and second is a range.

Now here we have to give the key of "SpreadsheetA", you can get the key of the any spreadsheet from the URL.

Look at following screenshot of "SpreadsheetA" (with "Sheet1" as active sheet):

In above screenshot, I have marked the key of "SpreadsheetA" with red color.

So in first argument of formula "ImportRange" we have to put key value: "0AmMTqpzD9YRndFRjTDBBc3VxTlFub0pTLUVKWkVIdVE"

And second argument is: CONCATENATE(A1;"!B4"), which will give us whatever contents of "Cell A1" (that is "Sheet1") + "!B4"

Look at the following screenshot of "SpreadsheetB":

In above screenshot of "SpreadsheetB", we are having "Sheet1" as value in "Cell A1", 

So formula CONCATENATE(A1;"!B4") will give us "Sheet1!B4".

And finally formula ImportRange will give us result: "Pink"

Look at the screenshot of "SpreadsheetB" above, Cell B3 in SpreadsheetB contains following formula:

=ImportRange("0AmMTqpzD9YRndFRjTDBBc3VxTlFub0pTLUVKWkVIdVE";CONCATENATE(A1;"!B4"))

I hope this will make you understand the formula ImportRange.

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.

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

How to unhide a sheet of a Spreadsheet

Following are the steps to unhide any hidden sheet(s) in your spreadsheet:

Step 1) Click on the View menu and then click on "Hidden Sheets".

Look at the following screenshots:

Note: If your spreadsheet don’t have any hidden sheet, then this option will be there.
By doing so, it will display all of your hidden sheets.

Step 2) Click on the sheet that you want to unhide.

By doing so, your sheet now be displayed with the rest of your sheets at the bottom of your spreadsheet.

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.

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

How to hide a sheet of a spreadsheet from view

How to hide a sheet of a spreadsheet from view

Google spreadsheets allows you to take individual sheets out of view by hiding the sheet. This feature is particularly useful if you have individual sheets that are old, rarely used, or are placeholders for calculations used by other sheets and you may have several other reasons so you may think these sheets must remain hidden.

Note:

Hiding a sheet does not mean the same as protecting a sheet. All users, with whom spreadsheet is shared, will be able to unhide and view these sheets. And spreadsheet viewers will not be able to view hidden sheets.

Following are the steps to hide a sheet in your spreadsheet:

Step 1) Click on the sheet tab that you want to hide (at the bottom left side of your spreadsheet).

Look at the screenshot below:

Then select "Hide sheet" from that menu. Note: If your spreadsheet does not have two or more sheets, then you may not see this option.

After following these steps, your sheet will be hidden from view.

Click here to know "How to unhide sheet".

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.

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

How to check who has viewed your shared Spreadsheet and at what time.

How to check who has viewed your shared Spreadsheet and at what time.

Question:

If you have shared a spreadsheet with many coworkers to view only so they have access to the information at any time, Then is there a way to see who has opened it when they are not making any changes?


Answer:

NO, you can't if you have shared your sheet in "view only" mode.

But YES, if you have shared it in "can edit" mode, then you can have this following script that will be executed whenever any user opens (access) it. And it will record the time (when sheet gets opened) in "Column A" and user's email Id in "Column B".

Note: This cannot be done if you share your spreadsheet in "view only", because then you are not allowing that user to edit your spreadsheet, so even this script will not have the permission to edit this spreadsheet.

Instead of sharing whole spreadsheet in "view only" mode, you can do the following:
1) Share your spreadsheet in "can edit" mode with your co-workers or friends.
2) Protect all the sheets, except one sheet and name it as "WhoHasAccessed" or whatever you want. So now by doing this, only one sheet can be edited by your co-workers or friends and you have protected all the other sheets.
3) Hide this "WhoHasAccessed" sheet. Click here if you don't know how to hide a Sheet. So by doing this, you will hide the data of this sheet. 
(But remember any of your co-worker and friend can easily view this hidden sheet and can view and edit data of this sheet. To avoid this, you can have a script which will immediately send you email with an attachment of this spreadsheet.)


Now, before explaining further, let us have a look at the following script with an example:



SCRIPT:

function onOpen() {
 WhoHasAccessed();
}

function WhoHasAccessed() {
  var userEmail = Session.getEffectiveUser();
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName("WhoHasAccessed");
  var range = sheet.getDataRange();
  var lastRow = range.getLastRow();
  var currentTime = Date();
  range.offset(lastRow, 0, 1, 1).setValue(currentTime);
  range.offset(lastRow, 1, 1, 1).setValue(userEmail);
}

The above script will record the time when sheet gets opened, in "Column A", and User's Email Id in "Column B".


Example:

Suppose you are "USER A" owner of a spreadsheet and you have shared it with "USER B" AND "USER C"

Now, if you shared this spreadsheet in 
"view only" mode with "USER A" and 
"can edit mode" with "USER B".
Then whenever "USER A" opens this spreadsheet, this script will not be executed successfully as "USER A" don't have the rights to edit this spreadsheet.
And whenever "USER B" opens this spreadsheet, this script will record the time and Email ID of "USER B".


Following is the screenshot of "WhoHasAccessed" Sheet:

I hope this script will help you to find out who has accessed your spreadsheet and at what time.

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.

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

Filter formula and comparing dates

Filter formula and comparing dates

Question:

How we can count, if we have data in a spreadsheet and we want to filter data and then Count in between specific dates.

For example:

Look at the following screenshot of a spreadsheet containing data:

So now we want to calculate no. of rows that have:

"A&C" in "Column K"

"enrolled" in "Column O"

Dates between "03/2/2012" and "03/28/2012" in "Column K"

"unknown" in "Column Q"

Solution:

Here is the formula:

=COUNTA(IFERROR(FILTER('Individual Status'!K:K;'Individual Status'!O:O="enrolled";'Individual Status'!P:P >= DATE(2012;3;2);'Individual Status'!P:P <= DATE(2012;3;28);'Individual Status'!K:K="A&C";'Individual Status'!Q:Q="unknown")))

The result of this formula in this example would be 2, as in above table (screenshot of spreadsheet), we have two rows that satisfy all conditions.

I hope you this tutorial will help you understanding the filtering of data by comparing dates.

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.

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

Conditional format to a drop down list item in spreadsheet

Conditional format to a drop down list item in spreadsheet

If you are wondering, how to conditionally format to a drop down list item in spreadsheet?

Then follow these steps to do it:

Step 1) Create a drop down list:

To create a drop down list, you have to click on Data at Menu Bar, and then click "Validation".

Look at the following screenshot:

In "Data validation", you need to select Criteria "Items from a list", and then you can either create list from range or you can enter list items. After that save it.

Look at the following screenshot:

For this example, I have entered items in the list as "Kishan,Rick,John,Adam", I have this validation in Cell A1.

Look at the following screenshot:

Step 2) Create Conditional Formatting:

To create Conditional Formatting, you have to click on Format at Menu Bar, and then click "Conditional formatting...".

Look at the following screenshot:

In "Conditional formatting", you need to select condition, in my example I have selected "Text contains" "Kishan", and then you can change the text color and backround color, in my example I have selected text color "RED" and background color "Light Blue". After that save it.

Look at the following screenshot:

Now you can see, I have selected Kishan in Cell A1, so it's text color will change to red and background color will change to light blue color.

Look at the following screenshot:

So this  was the "Step by Step tutorial of Conditional formatting to a drop down list item in spreadsheet". I hope it will help you.

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.

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