Change Background Color Of Google Document

Follow the steps that are shown below, to change the color of Google Document.

Go to menu "File" > "Page setup...":

Then choose the "Page color" that you want in background:



And then click "OK".

Final result:

And you are done!

Hope this helps.

Google Spreadsheet Script to Change Color of Cell(s) Having Minimum Value

Question:

( by Kaido Põder )

I have range A1:A9 numbers. In Google Spreadsheet, How can I change color of cell having minimum value.

Solution:

Have a look at the following screenshot:

Have a look at the following code:

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

// This script has been developed by Kishan
// For more visit: iGoogleDrive.blogspot.com

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Sheet1");
var r = s.getRange("A1:A9");

function onEdit(e) {
  var activeSheet = ss.getActiveSheet();
  var activeRange = activeSheet.getActiveRange();
  var sortedValues;
  if( s.getName() == activeSheet.getName() &&
    activeRange.getLastRow() >= r.getRow() && 
    activeRange.getRow() <= r.getLastRow() &&
    activeRange.getLastColumn() >= r.getColumn() && 
    activeRange.getColumn() <= r.getLastColumn() )
    { 
      check();
    }
};

function check() {
  var v = r.getValues();
  var minValue;
  var flag=false;
  
  for(var i=0;i<v.length;i++)
    for(var j=0;j<v[0].length;j++) {
      if(v[i][j]!="" && flag==false) {
        minValue=v[i][j];
        flag=true;
      }
      else if(v[i][j]!="" && minValue>v[i][j]) minValue=v[i][j];
    }
  
  var rowcol = [];
  for(var i=0;i<v.length;i++)
    for(var j=0;j<v[0].length;j++)
      if(minValue == v[i][j])
        rowcol.push([i,j]);
  
  r.setBackground("White");
  for(var k=0;k<rowcol.length;k++)
    s.getRange(r.getRow()+rowcol[k][0],r.getColumn()+rowcol[k][1]).setBackground("Yellow");
};

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

put the above code in your script editor and change Sheet1 with your sheet name and A1:A9 with the range that you want to include to check for the minimum value. If you want to check for whole column A then put "A:A" and if you want to check for column A,B,C then put "A:C", so it will work for any range you provide...


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 Countif with multiple criteria

Question:

( by Ar974 )

Bonjour, Hello

example:

cells  :            ListPos= UH/UV/S1/S2/DC/P/RH/RS/GRE/FG/JK/ML/DE/CV/........  (From S1 to S25......)

another List    ListC= CA / RTT /P / CET....

column A :      date : 1 janv / 2 janv / 3 janv / 4 janv / 5 janv / 6  janv / 7 janv /........

column B:  Position : UH     / DH     /         / S1      / CA      / RTT     /           /......... :  ----> cell can be clean /  can be fill from values from ListC or ListPos

so  B column B has elements from the ListPos and ListC with different dates

I want to make the sum of numbers of these positions:   S1 UH S2

then my formula will be:    =COUNTif(B6:B40;"S1")+COUNTif(B6:B40;"UH")+COUNTif(B6:B40;"S2") this is ok

but if I want X criteria (more than 10 for example):  =COUNTif(B6:B40;"UH")+COUNTif(B6:B40;"UV")+COUNTif(B6:B40;"S1")+COUNTif(B6:B40;"S2")+COUNTif(B6:B40;"DC");;;;; IT IS VERY LONG .....AND TAKE TIME

Is there no a formula like that :   =COUNT(FILTER(B6:B40 ; B:B="S1:S25")) ???? COUNT(FILTER(B6:B40 ; B:B=ListPos)) ??? not working to. ????

Merci a l'avance.

Thanks for your help in advance.

Solution:

Have a look at the following screenshot:

In the above sheet I have the following formula in Cell D1:

=counta( iferror( filter( B6:B40; match(B6:B40;S1:S25;0) ) ) )

and the following formula in Cell E1:

=counta( iferror( filter( B6:B40; match(B6:B40;S1:S25;0) ) ) ) + counta( iferror( filter( B6:B40; match(B6:B40;T1:T25;0) ) ) )

and the following formula in Cell F1:

=counta(arrayformula(iferror(match(B6:B40;S1:S25;0);iferror(match(B6:B40;T1:T25;0)))))

In the above formulas, I have assumed that you have you have the ListC in Column T (that is T1:T25) you can change the range as per your requirement.

And if you have given the range "S1:S25" name as "ListPos" then you can replace it in the above formula

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 Group By

Question:

( by Mikkel Holm )

Hi,

I'm organizing the amount of sales for my company in a spreadsheet, and I would like to see how much provenue every agent generates.

I know to count the amount of orders pr agent, by using the countif formula, but I'm having a hard time figuring out how to sum the value of orders made by a specific agent.

In other words I'm searching for a formula to sum the values of column B if column A is "XX" in column F. For example in F2 should be the summed value of all the cells i column B, where the initials "MKH" is in the adjacent A cell.

	A	B	C	D	E	F
1	Name	Value	Occurrences pr name		Value pr name
2	MKH	100	MKH	4	MKH	?
3	LT	200	LT	3	LT	?
4	LT	200
5	LT	200
6	MKH	100
7	MKH	100
8	MKH	500

Does anyone have a good idea? Your help will be much appreciated.

Cheers

Mike.

Solution:

Have a look at the following screenshot:

In the above sheet I have the following formula in Cell D1:

=query(A:B;"select A,count(B),sum(B) where A<>'' group by A")

and if you want to label the column header then try the following formula:

=query(A:B;"select A,count(B),sum(B) where A<>'' group by A label count(B) 'Count',sum(B) 'Sum'")

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 Send Email only Active Sheet as PDF Attachment

Question:

( by Brian Galloway )

I use google docs to share with all of my employees our day to day schedule but I also like to email myself the pdf of the particular day.  The only thing when I send myself the pdf, it sends me a pdf with every sheet.  I am trying to figure a way to just send myself the sheet of the particular day. Does anyone have any ideas how to accomplish this?
Cheers, Brian

Solution:

Have a look at the following code:

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

// This script has been developed by Kishan
// For more visit: iGoogleDrive.blogspot.com

function onOpen(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Send ActiveSheet as PDF", functionName: "SendSheetAsPDF"});
  spreadsheet.addMenu("Send Email", menuEntries);
};

function SendSheetAsPDF() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = ss.getId(); 
  var sheet = ss.getActiveSheet();
  var email = "kishan.pionero@gmail.com";
  var subject = "SUBJECT HERE..!!";
  var body = "Body of email here..!!";
  var getSheetId = sheet.getSheetId().toString();
  var sheetName = sheet.getName();
  
  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="+getSheetId+"&portrait=true"+"&exportFormat=pdf";
  var result = UrlFetchApp.fetch(url , requestData);
  var contents = result.getContent();
  
  MailApp.sendEmail(email,subject,body,{attachments:[{fileName:sheetName+".pdf",content:contents,mimeType:"application//pdf"}]});
};

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

put the above code in your script editor, save it and then refresh your spreadsheet. You'll be able to see the custom menu "Send Email" under which you'll have sub menu "Send ActiveSheet as PDF".

Have a look at the following animated screenshot:

So now, when you click on this sub menu, you will get an email on the email Id provided with the active sheet as attachment in PDF format.

In above code change the email Id "kishan.pionero@gmail.com" to your email Id.


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 Calculations with last N rows

Question:

( by lunixer )

Here's a formula I want to calculate:

In column B I have distance. In column C I have minutes and in column D I have seconds.

I want to sum the last 21 rows of B and divide it by the sum of the last 21 rows of C and D converted to hours. I can do the conversion bit (C/60+D/3600) but I'm not quite sure how I would capture the last 21 rows in that way.

Thanks!

Solution:

Have a look at the following screenshot of Spreadsheet:

In the above sheet, I have the following formula in Cell F3:

=round( query( query( arrayformula( if( {1,1,1,0} ; B2:D ; row(B2:B) ) ) ; "select Col1,Col2,Col3 where Col1 is not null order by Col4 desc limit 21" ; 0 ) ; "select sum(Col1)/((sum(Col2)/60)+(sum(Col3)/3600)) label sum(Col1)/((sum(Col2)/60)+(sum(Col3)/3600)) '' " ; 0 ) ; 2 )

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 Create a graph of top 5 text strings

Question:

( by Laurie Tewksbury )

How can I count how often text occurs, then create a graphic to shop top the 5 text strings?
I have a list of usernames in a spreadsheet as they contribute to our social media site. I'm hoping to take that list of usernames and find a way to graphically represent the top 5 contributors. A pie graph would be ideal, but a list would work well, too. I'd love for it to update automatically as users continue to contribute to our site. Therefore, it'll always show the top 5 contributing users, but these will change as users contribute more or less.

Is this possible? THANKS!

Solution:

Have a look at the following screenshot of "Sheet1":

Have a look at the following screenshot of "Sheet2":

In the above sheet "Sheet2" I have the following formula in Cell A1:

=query('Sheet1'!A:C;"select C,count(B) where C<>'' group by C order by count(B) desc limit 5 label count(B) 'Total Participation' ";1)

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