Google Spreadsheet Wrap Text

Question:


By default Google docs/drive seems to line-wrap long text fields.  I'd like to be able to set the width and height of a cell and hide remaining text, aka how excel works.  Does anyone know how to do this?

Here's an example:

I really like long text fields for descriptions, comments, and other stored but less useful information



|I really like long text fields f|


Solution:

You have to select the Cell and then click on the "Wrap text".

Have a look at the screenshot below:

And Note:

If you don't have blank spaces in text string, then you will not be able to wrap the text, look at the following screenshot:

In above screenshot, I have text "Hello My Name Is Kishan" in Cell A1,
And "HelloMyNameIsKishan" in Cell A3...
So you can see the results Cell A3 will not wrap as it don't have blank spaces.


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

Merging data from different Spreadsheets into a Master Spreadsheet

Question:

Hi,

I have about 15 spreadsheets that I need to take certain bits of information from and create a master sheet, I have an example spreadsheet here: 

What I am trying to do is take only the rows with the initials LD, AD and JL in the 'Who' column. I need to do this for about 15 different spreadsheets.

Any help would be great :)

Solution:

I assume, you must be having same number of columns in all spreadsheets that you want to merge and filter data from.

Here in this example, I am considering just three spreadsheets:

Spreadsheet 1:

Spreadsheet 2:

Spreadsheet 3:

Now, put the following formula in your master spreadsheet which will fetch records from all the spreadsheets:

=query(VMerge(ImportRange("0AmMTqpzD9YRndHdhaU90Y2tHSmRCaUExZzVuSXdIcGc";"Work list!A4:M");ImportRange("0AmMTqpzD9YRndEtXcTVGd1NYdlBZLUhIQUtsUFdQcHc";"Work list!A4:M");ImportRange("0AmMTqpzD9YRndDZ0MWZ6R1BXd1c0MXRDSm1xajRmRUE";"Work list!A4:M"));"select * where Col10 = 'AD' or Col10 = 'LD' or Col10 = 'JL'";0)

Note: The above formula contains spreadsheet keys in ImportRange formula, if you are not familiar with ImportRange then have a look at the following link:

http://igoogledrive.blogspot.in/2012/09/google-spreadsheet-importrange.html

Here is screenshot of the Master Spreadsheet:

You can notice master spreadsheet has all the records from three spreadsheets which are having "JL" and "AD" and "LD" in Column J.

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 results with multiple conditions

Question:

(by Kirsten)

I'm using a Mac to make a spreadsheet of student data (see attached file).

In Column B, I have different grades (6,7,8,9,10,11,12)

In Column C, I have different levels (beginner, intermediate, advanced)

Depending on how these two pieces of data combine, Column D ("Total Weekly Periods Required") will change.

Some examples: 

Grade 6, Beginner needs 6 periods. I would like the number "6" to appear in Column D whenever Column B says "6" AND Column C says "Beginner."

Grade 12, Advanced needs 3 periods. I would like the number "3" to appear in Column D whenever Column B says "12" AND Column C says "Advanced."

There are 21 different combinations, and I do not know how to best format it so that Column D can populate itself. Help would be much appreciated.

Solution:

Make a sheet "Sheet2", which will have all the 21 records, have a look at the following screenshot:

And then in Sheet "Sheet 1", you need to put the following formula in Cell "D2":

=filter('Sheet2'!C2:C22;match(concat('Sheet2'!A2:A22;'Sheet2'!B2:B22);concat(B2:B;C2:C);0))

Have a look at the following screenshot:

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

Counting Unique Colors in a Range in Google Spreadsheet

Counting Unique Colors in a Range in Google Spreadsheet

Following is the script for Counting Unique Colors in a Range:


function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "CountColors", functionName: "CountUniqueColors"}];
  ss.addMenu("CountColors", menuEntries);
}

function CountUniqueColors() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1"); //Change sheet name as per your requirement

  var colorRange = s.getRange("A1:A100"); //You can change this range as per your requirement
  var BackGroundColors = colorRange.getBackgroundColors();

  var counter = 0;

  var ColorArray  = new Array();
  ColorArray.push(BackGroundColors[0][0]);


  for(var row = 0; row < BackGroundColors.length; row++ )
  {
    for(var col = 0; col < BackGroundColors[0].length; col++ )
    {
      var colorFound = false;
   
      for (var j in ColorArray)
      {
        if( ColorArray[j] == BackGroundColors[row][col] )
        {
          colorFound = true;
        }
      }
   
      if(colorFound == false)
      {
        ColorArray.push(BackGroundColors[row][col]);
        counter++;
      }
    }
  }

  Browser.msgBox(counter);
}


I have used message box to display the result, which can be changed according to your requirement.

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.

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

Format range as Plain text in Google Spreadsheet

You can format any range (that can be any cells, columns, rows) as Plain text in Google Spreadsheet, so that instead of getting your default input as date or any other format, you will have your input data as Plain text.

To format your range as "Plain text", you have to select your range and then go to "Format" menu, then in "Number", click on "Plain text"

Have a look at the following screenshot:

After formatting your range, you can put your data into it.. and it will remain as plain text..

So even if you input "01-02" it will not convert to date and it will remain as a plain text.

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

Auto delete old entries from Google Spreadsheet submitted using forms

Auto delete old entries from Google Spreadsheet submitted using forms

Following is a script that can be set (triggered) to auto deletes old entries every day from Google Spreadsheet submitted using forms:

Insert the following script in your spreadsheet project using script editor:


function DeleteOldEntries() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //give your sheet name below instead of Sheet1
  var sheet = ss.getSheetByName("Foglio1");
  
  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();


  var currentDate = new Date();
  var oneweekago = new Date();
  oneweekago.setDate(currentDate.getDate() - 7);
    
  for (i=lastrow;i>=2;i--) {
    var tempdate = sheet.getRange(i, 1).getValue();
    
    if(tempdate < oneweekago)
    {
      sheet.deleteRow(i);
    }
  }
}

The above script will delete rows (entries) that are one week old, you can change the duration in it as per your requirement.

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 

You have to add triggers so that it executes it daily (or weekly or as per your requirement)

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

Alternate for Excel function AVERAGEIFS in Google Spreadsheet

Question:

I have a spreadsheet that works perfecctly in excel but when I upload to google one field shows an error "Name".

The formula I'm using is: =AVERAGEIFS(F3:F500,G3:G500,"w")
This works in excel and whait it does is looks in column G for a cell with a w in it, it then works out the average for the figures in column F when next to a "w" in G


Solution:

Here are some ways by which you can achieve the same thing in Google Spreadsheet, that can be acheived by AVERAGEIFS in EXCEL:

=SUMIF(G3:G;"w";F3:F)/COUNTIF(G3:G;"w") 

OR

=AVERAGE(FILTER(F3:F;G3:G="w")) 

OR

=AVERAGE(QUERY(F3:G;"select F where G = 'w'")) 

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