Google Spreadsheet Synchronize Two Sheets

Google Spreadsheet Synchronize Two Sheets

Question:

(by Omar)

I have 2 worksheets. Sheet1 and Sheet2

I want to have cell A1 on Sheet1 to be shown on Sheet2 - I can do this

BUT... what I want is to be able to change the value in both places - a change made on Sheet 2, gets shown on Sheet 1

And a change made on Sheet 1 shoes on Sheet 2

How can I do?

Thanks

Solution:

Now, have a look at the following script:

function onEdit()

{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var activeSheetName = ss.getActiveSheet().getSheetName();
  
  if( activeSheetName == "Sheet1" || activeSheetName == "Sheet2" )
  {
    var activeCell = activeSheet.getActiveCell();
    var activeCellinA1 = activeCell.getA1Notation();
    
    if( activeCellinA1 == "A1" )
    {
      var activeCellValue = activeCell.getValue();
      if( activeSheetName == "Sheet1" )
        ss.getSheetByName("Sheet2").getRange("A1").setValue(activeCellValue);
      if( activeSheetName == "Sheet2" )
        ss.getSheetByName("Sheet1").getRange("A1").setValue(activeCellValue);
    }      
  }
}



When you edit Cell A1 in either Sheet1 or Sheet2 then the above script will run the onEdit function, and Cell A1 on both the sheets that is Sheet1 and Sheet2 will automatically synchronize.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/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 Script to go to Next Sheet

Google Spreadsheet Script to go to Next Sheet

Question:

(by Aaron Tawil)

So I am trying to create a click-through by assigning a script to a 'next' button that allows the user to click on the button and it will move them from the current sheet to the next sheet.

I get this error when I click the button: Cannot find method (class)(class)setActiveSheet(number)

this is my code:




function nextMonth(){

  var sheet = SpreadsheetApp.getActiveSpreadsheet()

  var id = sheet.getSheetId();

  if(id == 11){

    id = 0;

  }

  else{

    id += 1;

  }

  sheet.setActiveSheet(id);

}

Solution:

Now, have a look at the following script:




function switchToNextSheet()

{

  var ss = SpreadsheetApp.getActiveSpreadsheet()

  var id = ss.getSheetId();

  var sheets =  ss.getSheets();

  

  var ids =[];

  var currentid = 0;

  for( var i=0;i<sheets.length;i++)

  {

    ids[i] = sheets[i].getSheetId();

    if( id == ids[i] )

      currentid = i;

  }

  

  var nextid = 0;

  

  if( currentid == sheets.length-1 )

    nextid = ids[0];

  else

    nextid = currentid+1;

  

  ss.setActiveSheet(sheets[nextid]);

}

When you run the above function, you will move to the next sheet of your current active 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.

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 Calculate Date and Time Difference

Google Spreadsheet Calculate Date and Time Difference

Question:

(by Czar Augusto)

I'm trying subtract date+hour between two cells.

In one of the cells, i would like insert manually date and times values. In other cells i used the function =NOW which automatically insert values of date and time current.

How to achieve this using google spreadsheet?

Solution:

Now, have a look at the following screenshot:

Here in the above sheet,

Cell B1 contains following formula:
=now()


Cell B2 contains following value:
6/1/2012 20:27:20


Now, put the following formula in Cell B5:
=rounddown(B1-B2) & " days, " & hour(B1-B2) & " hours, " & minute(B1-B2) & " minutes and " & second(B1-B2) & " seconds."


Or use the following formula:
=rounddown(B1-B2) & " days and " & time(hour(B1-B2);minute(B1-B2);second(B1-B2)) & " hours."


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 Average function

Question:

This is probably a very simple question, but I'm really at a loss as to what I'd search for to find this function if it exists at all.

 See, I use "=AVERAGE(E2:E37)" as an example in one of my spreadsheets, but problem is, every month I have to change "e37" to the next up grid so, next month would be "e38", I want to be able to just do something like this: "=AVERAGE(E2:H64)" and have H64 retain the actual value that I want to use for that month, so "H64" would have the "38" or line value so that I only have to update one value every month. 

Here is a piece of the spreadsheet hopefully it helps explains my issue.

Solution:

I would suggest instead of Averaging the values at the bottom, you can try it in the first row, have a look at the following screenshot:

Formula in Cell C1: =AVERAGE(C2:C)

Formula in Cell D1: =AVERAGE(D2:D)


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 Query for Room Reporting Project

Question:

Hi there,

I need help with the following sheet:

The Form Responses is a type of scoring system for peoples rooms. The rooms are scored according to Good, Ok or Bad.
On the Ad Hoc Reporting tab I want to able to filter those results according to Person, Zone, and between certain dates, but I still want the whole line -
Timestamp Person Zone  [Bed]  [Closet]  [Clothing]  [Hygene]  [Dishes]     -
and the data to pull through.
From there I can then create reports based on each individuals results.(see sheet2)
If someone can help me with a formula to do just that I will be #$%^&* greatful , cause I have tried and tried but no luck!
Thanks,


Solution:

Now put the following formula in Cell A7:

=query('Form Responses'!A:H;"select * where B = '"&B5&"' and C = '" &D5& "'")

It will provide you the following results:

Use the following formula for "ALL" case (either in Cell B5 or in Cell D5):

=if(and(B5="All";D5="All");query('Form Responses'!A:H;"select *");if(B5="All";query('Form Responses'!A:H;"select * where C = '" &D5& "'");if(D5="All";query('Form Responses'!A:H;"select * where B = '"&B5&"'");query('Form Responses'!A:H;"select * where B = '"&B5&"' and C = '" &D5& "'"))))


And use the following formula for also comparing the date which are in Cell F5 and Cell F6:

=if(and(B5="All";D5="All");query('Form Responses'!A:H;"select * where toDate(A) >= date '" & text(F5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(F6,"yyyy-MM-dd") &"'");if(B5="All";query('Form Responses'!A:H;"select * where C = '" &D5& "' and toDate(A) >= date '" & text(F5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(F6,"yyyy-MM-dd") &"'");if(D5="All";query('Form Responses'!A:H;"select * where B = '"&B5&"' and toDate(A) >= date '" & text(F5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(F6,"yyyy-MM-dd") &"'");query('Form Responses'!A:H;"select * where B = '"&B5&"' and C = '" &D5& "' and toDate(A) >= date '" & text(F5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(F6,"yyyy-MM-dd") &"'"))))


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

Introducing the AdSense 10 Challenge

Celebrating 10th Anniversary of Google Adsense

Google Adsense has completed successful 10 years and so they are hosting a free ten week class to help us become an AdSense expert and grow our revenue. This upcoming Learn with Google for Publishers series, the AdSense 10 Challenge, starts on June 25th.

Now let us see what exactly is the AdSense 10 Challenge? 
We will learn how to make the most out of your online business with AdSense. This class will cover a range of topics and it will make us learn how to increase our revenue, uncover new information in our reports, and set up experiments to determine the best layouts for our website and blog. We’ll meet members of the AdSense community like optimization specialists and experienced AdSense publishers.

When is the Challenge and what topics will it cover? 

The AdSense 10 Challenge will take place online from June 25th - August 30th. We’ll cover a wide range of topics designed to encourage AdSense success.

And now we are also able to play game on our Google Adsense home page, here is the video of me playing against CPU.

Check out the following video:

I won, and I liked this game :)

Have a look at the following screenshots:

I hope you liked this post. Keep googling and keep earning with Adsense.

Also check out the following link: Tech and Non-Tech Wisdom


Thanks,

Kishan.

igoogledrive.blogspot.com

Copying Table from Google Spreadsheet

Question:

I tried to copy a table from Spreadsheet to Presentation. But the Boaders is lost. How to do? Actually, this happens in all google products. For example, from Spreadsheet to Gmail.

Solution:

Copy your table from Google Spreadsheet:

And then if you directly paste it to Google Presentations then you are not going to get the borders, I think that's an issue with Copy Pasting data with many Cloud products.

Anyways here's the work around for your requirement, though its a bit lengthy process but you will get your results. So now, after copying data from Google Spreadsheet, paste it to the Microsoft Excel.

After pasting it to the MS Excel, again copy it from MS Excel, and now make a table in your Google Presentation. Table with one row and one column will also do, as it will auto increase the number of rows and columns according to the copied data from MS Excel.

 And now paste the data that you copied from MS Excel to the table that you created in Google Presentation.

Have a look at the screenshot below:

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

Thanks,

Kishan.

iGoogleDrive