How to set a trigger on a script in Google Spreadsheet

How to set a trigger on a script to execute a function at a particular event.

Question:


How to set a trigger on a script to execute a function at a particular event.



Solution:

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 "yourfunction" and in Events click on "Time-driven" and then select at whatever frequency you want to execute this trigger. Have a look at the screenshot below:

I hope this script would have solved your problems to set the trigger on spreadsheet that will execute on a specified event.

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 capture the value of the stock price

How to capture the value of the stock price so that it is static and not update any more?

Question:

How to log the price of a stock at 15 minute increments during the day?

How to figure out that 15 minutes has indeed passed by?

How to capture the value of the stock price so that it is static and not update any more?

Solution:

Here is the script that will do your job:

function myFunction()

{

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  var sheet = ss.getSheetByName("Sheet1");

  var range = sheet.getDataRange();

  var lastRow = range.getLastRow();

  var currentTime = Utilities.formatDate(new Date(), "GMT+0530", "dd-MM-yy HH:mm");

  var stockprice =range.offset(0,0,1,1).getValue(); // this is for Cell A1

  range.offset(lastRow, 0, 1, 1).setValue(currentTime);

  range.offset(lastRow, 1, 1, 1).setValue(stockprice);

}

Requirements for the above script example:

You must have the formula =googlefinance("GOOG","price") or any such formula in Cell A1, which will be recorded along with the time in the last row of the sheet.

If you are not familiar with writing scripts and don't know where to start from then have a look at the following link:

Here is post for how to work with scripting in google spreadsheets:

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

Now to set the timer to record this stock price at every 15 mins, you have to set the trigger for that. Following link will make you understand how to set a trigger on an event:

http://igoogledrive.blogspot.in/2012/08/how-to-set-trigger-on-script-in-google.html

I hope above post will help you to understand and to record stock prices with the help of the script.

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 

Cheers!!

Kishan,

iGoogleDrive

Rounding value of a cell to the nearest quarter

How to write a formula which will automatically round the value of a cell to the nearest quarter?

Question:

How to write a formula which will automatically round the value of a cell to the nearest quarter?

Suppose if you are having value 2.19 in Cell A1 and you want to convert it to the nearest quarter value, that is convert 2.19 to 2.25, and display it to the Cell B1.

Solution:

Insert the following formula to the Cell B1:
=if(mod(A1;0.25)<0.125;A1-mod(A1;0.25);A1+(0.25 - mod(A1;0.25)))

This will give you the result:
2.25 in the Cell B1.

And if you are having 2.39 then it will convert it to 2.5 (as it is nearest quarter value to 2.39)

I hope this helps 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

How to know who has modified a cell and at what time

How to know who has modified a cell and at what time

Following script will let you know which cell has been modified by which user, in the comment of that particular cell itself:


function onEdit(e)
{
  myFunction();
}

function myFunction()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var date = Utilities.formatDate(new Date(), "GMT+0530", "dd-MM-yy HH:mm");
  var actRng = sheet.getActiveRange();
  var rowindex = actRng.getRowIndex();
  var colindex = actRng.getColumnIndex();
  var thisCell = sheet.getRange(rowindex,colindex);

  var userEmail = Session.getEffectiveUser();
  thisCell.setComment("'" + date + " " + userEmail);
}

If you don't know how to write a script, or where to write this above script then look at the following link which will help you out:

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.

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 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.

Example for Auto Increment In Library Management

Example for Auto Increment In Library Management

Question:

In my office I have a big workbook with several different sheets and, for better structure, there are one workbook for each month.

There are a few auto-increments in these workbooks, which means the first value must be the last value, from last month incremented by one.

Once I need to duplicate the "Template Workbook" every month before start using it, I decided to add a group of cells from which I can do some sort of configurations:

+-----------------------+

|      Last Records     |

|   (from Last Month)   |

+-----------------------+

| Book ID | Last Record |

+---------+-------------+

|   A15   |    1.234    |

+---------+-------------+

|   B28   |    5.678    |

+---------+-------------+

|   C05   |    9.012    |

+-----------------------+

For future reference, let's name this range as Q1:R3.

In the previous version of this "Template Workbook", it was an easy task because each Book ID had its own sheet.

But I was wasting my working time by opening between all the sheets all the day. And then, I decided to add all records in a single sheet and when I need some specific data, I just have to query them, just like a database:

+---------+-------------+

| Book ID | Next Record |

+---------+-------------+

|   A15   |    1.235    |

+---------+-------------+

|   B28   |    5.679    |

+---------+-------------+

|   A15   |    1.236    |

+---------+-------------+

|   B28   |    5.680    |

+---------+-------------+

|   C05   |    9.013    |

+---------+-------------+

For future reference, let's name this range as A1:B5

And now the real problem about the auto-incrementing, to be added in Column B:

I need to check if the value entered in Column A is the first occurrence of that value in entire Column.

If so, the value to be added in Column B will be what I have defined in R1 PLUS 1, because it refers to the first record, of that book in that month.

If it's NOT the first occurrence, I need to find the penultimate occurence of that Book ID in Column A, grab the value of its Column B and then increment it.

Applying this idea to table above, it would be:

+---------+-------------+----------+---------+

| Book ID | Next Record |  First?  |   Use   |

+---------+-------------+----------+---------+

|   A15   |    1.235    |    YES   |  R1 + 1 |

+---------+-------------+--------------------+

|   B28   |    5.679    |    YES   |  R2 + 1 |

+---------+-------------+--------------------+

|   A15   |    1.236    |    NO    |  B1 + 1 |

+---------+-------------+--------------------+

|   B28   |    5.680    |    NO    |  B2 + 1 |

+---------+-------------+--------------------+

|   C05   |    9.013    |    YES   |  R3 + 1 |

+---------+-------------+--------------------+

Answer:

Following is the screenshot of the spreadsheet created for solving the above question:

In above example (screenshot), we have created both the ranges i.e, Q1:R3 and A1:B5 in the same (single) sheet. 

Following is the formula, which will give the required result by inserting it in the Cell B1 and then dragging it down up to the required row:

=IF(COUNTIF(INDIRECT(CONCATENATE("A1:A";ROW()-1));INDIRECT(CONCATENATE("A";ROW())))=0;VLOOKUP(INDIRECT(CONCATENATE("A";ROW()));Q:R;2)+0.001;MAX(FILTER(INDIRECT(CONCATENATE("B1:B";ROW()-1));INDIRECT(CONCATENATE("A1:A";ROW()-1))=INDIRECT(CONCATENATE("A";ROW()))))+0.001)


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 know which user modified the row

How to know which user modified the row

Are you wondering about how to know which user (collaborator) modified the row?

Following action works for all users (collaborators) including owner:

Action:

When any collaborator modified a row in the spreadsheet this script will add "the date and time" in the last cell of that row and will add a comment in that last cell with email address of that collaborator who had modified that row.

The following script will work when either the owner or any other collaborator of a spreadsheet modified that spreadsheet:

function onEdit(event)
{ 
  
  var sheet = event.source.getActiveSheet();
  
  // Note: actRng = return the last cell of the row modified
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();
  
  // Note: date = return date + time
  // Note: user = return the user email
  var dateCol = sheet.getLastColumn();  
  var lastCell = sheet.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), "GMT-0300", "dd-MM-yy HH:mm");
  
  //var user = event.user;  // Note: event.user will not give you collaborator's Id
  var user = Session.getEffectiveUser();
  
  // Note: setcomment = Inset a comment with the user email
  // Note: setValue = Insert in the cell the date + time when this row was modified
  lastCell.setValue("'" + date).setComment(user);
}

I hope this script will do your task of getting to know which collaborator has modified which row and at what time.

If you are new to Google Spreadsheet Scripts, then you can 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.

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 get the location of last modified Cell

How to get the location of last modified Cell

Following script will get you the location of last modified Cell:


function setTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("myFunction").forSpreadsheet(ss).onEdit().create();
}

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var sName = sheet.getName();
  var currentCell = sheet.getActiveCell().getA1Notation();
  UserProperties.setProperty("mySheetName", sName);
  UserProperties.setProperty("myCell", currentCell);
}

function onOpen() {
  var lastModifiedSheet = UserProperties.getProperty("mySheetName");
  var lastModifiedCell  = UserProperties.getProperty("myCell");
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(lastModifiedSheet).getRange(lastModifiedCell).activate();
}


When you run it for the first time, it will ask you for the authorization of this script as it is modifying User properties. 

You have to wait for few seconds when you open your sheet, to get the position of your lastly modified cell (as it takes time for a script to execute).



If you don't know how to write a script, or where to write this above script then look at the following link which will help you out:
http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

I hope this script will help you to get the location of last modified 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

Lock down cells with Protected Ranges in Google spreadsheets

Lock down cells with Protected Ranges in Google spreadsheets

Following is the step by step tutorial to protect ranges (cells or rows or columns):

Step 1:

Click on the cell(s) or row(s) or column(s) which you want to protect editing from others.

Following is the screenshot of my spreadsheet, in which I have selected Column C, that I want to protect:

Step 2 (Option 1):

Go to "Data"  menu and click on "Named and protected ranges...".
Look at the screenshot below:

Step 2 (Option 2):

Or you can also first select cell(s) or row(s) or column(s) and then "right click" on it and then click on "Name and protect range.."
Look at the screenshot below:

Step 3:

Now you can give name and provide any range to protect. Click (select or tick mark) on "Protect".
Look at the following screenshot:

In above example I have protected column C of Sheet1, so I have "Sheet1C:C" as a range and named it "NamedRange1".

I hope this will help you to understand how create and name protected ranges.

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

Filtering data on multiple sheet by comparing columns

Filtering data on multiple sheet by comparing columns

Question:

I have spent hours making a database with multiple sheets. one with properties i have available and one with applicants looking for properties. I understand the basics of conditional formatting and if statements but i am now struggling. is there a formula that will match alert me if there are any matches between the two sheets. for example.

Sheet1 (Screenshot):

Sheet2 (Screenshot):

I would like the sheet to match, column B with B, C with C and D with D. so if the budget, price and move dates match it tells me in a separate cell or sheet would be even better.


Solution:

If you want to get alerted even if only Column B of Sheet1 matches to Column B of Sheet2 then put the following formulas in the mentioned below Cells on Sheet 3:
at Cell A1 on sheet3: =ARRAYFORMULA(IF(Sheet1!A1:A<>"";IF(Sheet1!A1:A=Sheet2!A1:A;"MATCHES";"");""))
at Cell A2 on sheet3: =ARRAYFORMULA(IF(Sheet1!B1:B<>"";IF(Sheet1!B1:B=Sheet2!B1:B;"MATCHES";"");""))
at Cell A3 on sheet3: =ARRAYFORMULA(IF(Sheet1!C1:C<>"";IF(Sheet1!C1:C=Sheet2!C1:C;"MATCHES";"");""))
at Cell A4 on sheet3: =ARRAYFORMULA(IF(Sheet1!D1:D<>"";IF(Sheet1!D1:D=Sheet2!D1:D;"MATCHES";"");""))

And if you want directly when all three conditions match then you can directly insert the following  formula:
at A5 on sheet3: =ARRAYFORMULA(IF(Sheet1!D1:D<>"";IF(Sheet1!B1:B=Sheet2!B1:B;IF(Sheet1!C1:C=Sheet2!C1:C;IF(Sheet1!D1:D=Sheet2!D1:D;"MATCHES";"");"");"");""))

You can see the results of above two sheets (sheet1 and sheet2) in the following screenshot of Sheet3, Cell A2 and Cell A4 are matching, Similarly Cell B3 and B4 are matching.

Sheet3 (Screenshot):

Look at the following Screenshot of the Sheet4, if you want only the filtered results where:
Column B of Sheet1 matches Column B of Sheet2
Column C of Sheet1 matches Column C of Sheet2
Column D of Sheet1 matches Column D of Sheet2

I have inserted the following formula in Cell A1 of Sheet4:
=FILTER(Sheet1!A1:D;Sheet1!B1:B=Sheet2!B1:B;Sheet1!C1:C=Sheet2!C1:C;Sheet1!D1:D=Sheet2!D1:D)

Sheet4 (Screenshot):

I hope this will help you out.

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 sum a column that has "--" values.

How to sum a column that has "--" values.

Question:

The SUM formula at cell N2 results in "--"

This is because the CONTINUE function in E147 resulting from the VLOOKUP formula in E3 is yeilding a "--" (which is a result of the actions on the HTML Lookup sheet.)

Is there any way to get the sum formula to ignore errors and "--" results and sum the valid numerical results?



Solution:

Instead of =sum(N3:N162) , Try =SUM(FILTER(N3:N162;ISNUMBER(N3:N162)))

So to exclude all non-numerical values, Instead of using formula =SUM(F10:F15), you must use =SUM(FILTER(F10:F15;ISNUMBER(F10:F15)))

I hope this solution will help you out.

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

Validating a Cell having extra blank spaces

Validating a Cell having extra blank spaces

Question:

I am using data validation for a "Day of the week" column using items from a list.  Here is my list:

Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,

However, the people entering information are constantly entering a space after the day, eg "Sunday "  instead of "Sunday."

The data validation doesn't catch this.  What can I do?  Is there a way to force them to choose from a list instead of allowing them to type or choose?

Is there a way to tell data validation to not accept spaces?

Solution:

This can be done with the help of the following script:

I have written this following script for only Cell H2 and only for "Sunday". 
I have also put Data validation on Cell H2 for it should be only from list: 
Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday


function onEdit(e)
{
 myFunction();
}

function myFunction()
{  
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName("mainsheet"); // your sheet name
  var range = sheet.getDataRange();
  var CellValue = range.offset(1, 7, 1, 1).getValue(); // Cell H2
  
  if (CellValue.match(/.*Sunday/))
  {
    range.offset(1, 7, 1, 1).setValue("Sunday");
  }  
}

The above script will execute on every time when a user will edit this spreadsheet.

If user inputs "Sunday  " it will change it to "Sunday".

I hope this script helps you in validating your data.

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