Google Spreadsheet ImportRange In Reverse Order

Question:

( by Ernst103 )

Hi All!

For days I have searched for a formula that can help me with a import range that update in reverse order, so to speak, no success.

I have data sheet 'SMSDB" with 5000 plus lines, and every day it updates with sms's (there is only 100 or so numbers in the entries). I import and filter the the data in a separate sheet. All works well, From the first date the value is imported to the 1st column, tomorrow the 2nd value appears in the 2nd column and so on,, sweet.

But now after 150 days there is 150 columns and you have to scroll all the way to the end to see the latest value, so IS it possible to have the range update the 1st column and move the whole range on one column?

Or what may also do the job is if the import range only import the 10 latest values from the SMSDB. The main idea is to see the latest 10 values, the farmer just needs to know if the latest value is up or down.  

The SMSDB:

The Import sheet:

Thank you all you geniuses out there!!!!

Ernst 

Solution:

Have a look at the following screenshot:

I have the following formula in Cell C4:

=Transpose( query( ImportRange("0AghC7uJYvbXYdEd1OFUtUmVMdUlXSmNRYnNFNXBCSXc","Sheet 1!A:F") ; "select Col6 where Col4='+27782437428' order by Col1 desc limit 10" ; 0 ) )

I have the following formula in Cell C5:
=Transpose( query( ImportRange("0AghC7uJYvbXYdEd1OFUtUmVMdUlXSmNRYnNFNXBCSXc","Sheet 1!A:F") ; "select Col6 where Col4='+27730467475' order by Col1 desc limit 10" ; 0 ) )

I have the following formula in Cell C6:
=Transpose( query( ImportRange("0AghC7uJYvbXYdEd1OFUtUmVMdUlXSmNRYnNFNXBCSXc","Sheet 1!A:F") ; "select Col6 where Col4='+27810565555' order by Col1 desc limit 10" ; 0 ) )

I have the following formula in Cell C7:
=Transpose( query( ImportRange("0AghC7uJYvbXYdEd1OFUtUmVMdUlXSmNRYnNFNXBCSXc","Sheet 1!A:F") ; "select Col6 where Col4='+27832477651' order by Col1 desc limit 10" ; 0 ) )

Similarly you can get for the remaining rows...

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

Question:

( by Daz A )

Right now I have a project that updates once a week, which is very easy to enter into Sheets: I just add in two Wednesday dates and drag down to auto-fill the column for every upcoming Wednesday. Super easy!

But in the future I'm switching to twice weekly updates, every Wednesday and Sunday. I've tried doing the same thing, this time entering in four dates to give Sheets a pattern to recognize and drag down... But this doesn't work. It comes off in a different format (including hourly time) and starts becoming inaccurate after more than two steps (so it switches to Thursday/Sunday). 

Is there any other way to get alternating dates like this to auto-fill? I want to check if there's any easier way to do this before trying to fill it in by hand or trying out Apps Script or something...

Solution:

Have a look at the following screenshot:

To get auto filled 10 dates starting from "3/15/2013", I have the following formula in Cell A1:

=arrayformula(if(row(A1:A10);datevalue("3/15/2013")+row(A1:A10)-1;""))

To get auto filled 10 dates starting from today(), I have the following formula in Cell C1:

=arrayformula(if(row(A1:A10);today()+row(A1:A10)-1;""))

To get only dates for only Wednesdays out of "10 dates starting from today()", I have the following formula in Cell E1:

=arrayformula(if(weekday(today()+row(A1:A10)-1)=4;today()+row(A1:A10)-1;""))

in the above formula 4 is for Wednesday..

To get only dates for only Wednesdays and Sundays, I have the following formula in Cell F1:

=arrayformula(if( (weekday(today()+row(A1:A10)-1)=4)+(weekday(today()+row(A1:A10)-1)=1) ;today()+row(A1:A10)-1;""))

in the above formula 4 is for Wednesday and 1 is for Sunday..

To avoid blank rows, I have the following formula in Cell G1:

=query(arrayformula(if(weekday(today()+row(A1:A10)-1)=4;today()+row(A1:A10)-1;""));"select Col1 where Col1 is not null";0)

And I have the following formula in Cell H1:

=query(arrayformula(if( (weekday(today()+row(A1:A10)-1)=4)+(weekday(today()+row(A1:A10)-1)=1) ;today()+row(A1:A10)-1;""));"select Col1 where Col1 is not null";0)

And finally, to get N dates which are Wednesdays and Sundays starting from today, I have the following formula in Cell I1:

=query(arrayformula(if( (weekday(today()+row(A1:A1000)-1)=4)+(weekday(today()+row(A1:A1000)-1)=1) ;today()+row(A1:A1000)-1;""));"select Col1 where Col1 is not null limit 20";0)

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

How to get the current filter value applied to a table in Google Docs Spreadsheets?

Question:

( by leniel )

Currently I have a table in a sheet called Timesheet. I use a Filter in the header of this table to filter by Project name. I have a SUBTOTAL formula that gives me a great figure about the total hours  for the current selected project.

Now what I'd like to do is: get the value of the current selected filter (only 1 item/project name in my case) and use it to match with the name of the project in another sheet called Projects where I have the project name in one column and hour value in another column. My desired output would be the total value (Subtotal) of Hours I have in sheet Timesheet multiplied by the Hour value present in sheet Projects.

Hope it makes sense. Here's a sample spreadsheet:

Sheet "Projects":

Sheet "Timesheet":

Is there any script I can attach to my spreadsheet or anything that can let me achieve what I want or is it even impossible right now?

I managed to get it to work in the sheet Projects but that's a static thing. I'd like to have the Total Earned present in the sheet Timesheet and see the value change dynamically according to the filter I have selected in column Project in the Timesheet.

Thanks for your attention,

Leniel

Solution:

Have a look at the following screenshot:

I have change the formula in Cell E2 to:

=INT(SUBTOTAL(109,D4:D) )*24+HOUR( SUBTOTAL(109,D4:D) )&":"&TEXT(MINUTE( SUBTOTAL(109,D4:D) ),"00")

And I have the following formula in Cell F4:

=arrayformula(iferror( round(((hour(D4:D)*3600)+(minute(D4:D)*60)+second(D4:D))/3600;2) * (if(E4:E="";"";vlookup(E4:E;Projects!E3:F;{2}*sign(row(E4:E));false)))))

And I have the following formula in Cell H2:

=INT( SUBTOTAL(109,F4:F) )

And now when you filter the range with "Project 1", you will get the following results:

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 How to use GoogleFinance to load a table with Date, Low, Open, Close, High

Question:

( by Mike del Caribe )

Hi I would like to use the GoogleFinance function to supply input data for a candlestick chart.  First a function should create a table of historical data of length defined by a start date and today's date variable.  I am using the Position Tracker with Chart Template that I downloaded from the Google Docs site.

When I use =GoogleFinance($M$3, "price",'Common Data'!$B$5,TODAY(),'Common Data'!B7)

where:

$M$3 is the symbol

price is the last price

'Common Data'!$B$5 is the spreadsheet location with the start date of the data

'Common Data'!B7 is the spreadsheet location with the weekly or daily variable

Using this formula I only get two columns with date and last price.

I have tried replacing price with a cell reference (say D42 which contains Low) that contains low, open, close, or high and again I get a 2D array with only date and price.

If I then try to specify a range of cells (say D42:D43 which contain Low and Open) I get the value error message "#Vale: Range has no entry corresponding to this Cell"

If I use the Index function, example: =Index(GoogleFinance($M$3, F43,'Common Data'!$B$5,TODAY(),'Common Data'!$B$7), 2,2), I can get the individual values for a single date, but the table isn't automatically filled in for the rest of the table like it is using the GoogleFinance function. I have tried copying the Continue function that GoogleFinance uses, but I get the say result that the table doesn't fill.

Please recommend what my options are.

Solution:

Have a look at the following screenshot:

And I have the following formula in Cell A6:

=googlefinance( B1 ; "all" ; B2 ; B3 )

General explanation:

=googlefinance( "SYMBOL" ; "all" ; "START_DATE" ; "END_DATE" ) 

And try the following formula if you want to get it only for a single date:

=googlefinance( "SYMBOL" ; "all" ; "DATE" ) 

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

In column B i have dates in format  in column D i have values.

what i want to do is sum each date seperately (so that i have a total of hours on 1/1/06 and then 1/2/06 and so on and so on without doing this manually- any formula suggestions greatly appreciated. 

Solution:

Have a look at the following screenshot:

And I have the following formula in Cell F1:

=query(A:D;"select B,sum(D) where B is not null group by B label B 'Date', sum(D) 'Total' ")

So now you have a single formula solution, and when you will update the data in range A:D, the data will auto update on columns F and G.

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 trim and clean data

Question:

( by Darren Tay )

A common problem when many people are contributing to a spreadsheet is dirty data entry, e.g. leading/trailing whitespace, CR / LF.

For some purposes, there are certain characters that are not accepted.

Is there a way to in-place TRIM and CLEAN?

The usual way I know is to create a new column and do =TRIM(otherColumn), and then you might have to convert from formula to value, then delete the source column?

I would also like to have Conditional Formatting to highlight dirty data. 

Presently the available types of Rules are not able to detect cells that need to be trimmed, unprintable chars etc.

Thanks very much

Solution:

Have a look at the following animated screenshot:

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: "Trim", functionName: "trim"});
  spreadsheet.addMenu("Script", menuEntries);
};

function trim() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");
  var r = s.getRange("A1:E5");
  // Change the range as per your requirement
  // Change the range "A:A" to only trim the values in Column A.
  var v = r.getValues();
  for(var i=0;i<v.length;i++)
    for(var j=0;j<v[0].length;j++)
      v[i][j]=v[i][j].toString().trim();
  r.setValues(v);
};

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

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet.

After inserting the above code in your spreadsheet, you can refresh your spreadsheet and then you would be able to see the custom menu "Script" on the menu bar. And then in menu "Script" click on the sub menu "Trim", then you will notice all the values getting trimmed.

I have marked the range with green color, so that you can edit as per your requirement. And if you want to trim the values on whole sheet then,
Instead of line:
var r = s.getRange("A1:E5");
Change it to:
var r = s.getDataRange();


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 move row from one sheet to another and have it time stamp a specific cell in that row

Question:

( by Matthew Beninato )

Below is my current script. What I'm trying to add to this is a function that will add a time stamp to another cell within the same row once the cell is marked completed. Can you help please???

function onedit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var targetSheetName = "Completed";

  var sourceSheetName = "Eligibility & Pre-Auths"

  var targetSheet = ss.getSheetByName(targetSheetName);

  var sourceSheet = ss.getActiveSheet();

  var sourceRow = sourceSheet.getActiveRange().getRow();

  var targetRow = targetSheet.getLastRow() + 1;

  

  //var rows = SpreadsheetApp.getActiveSheet().getCurrentRow();

  var cell  = SpreadsheetApp.getActiveSheet().getRange(sourceRow, 26);  

  var value = cell.getValue().toString();

  

  if (sourceSheet.getName() == sourceSheetName) {

    if (value=='Yes') { 

      if (targetRow > targetSheet.getMaxRows()) targetSheet.insertRowAfter(targetRow - 1);

      sourceSheet.getRange(sourceRow, 1, 1, sourceSheet.getLastColumn()).copyTo(targetSheet.getRange(targetRow, 1));

      sourceSheet.deleteRow(sourceRow);

    };

  };

}

Solution:

Try the following code:

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var targetSheetName = "Completed";

  var sourceSheetName = "Eligibility & Pre-Auths"

  var targetSheet = ss.getSheetByName(targetSheetName);

  var sourceSheet = ss.getActiveSheet();

  var sourceRow = sourceSheet.getActiveRange().getRow();

  var targetRow = targetSheet.getLastRow() + 1;

  

  //var rows = SpreadsheetApp.getActiveSheet().getCurrentRow();

  var cell  = SpreadsheetApp.getActiveSheet().getRange(sourceRow, 26);  

  var value = cell.getValue().toString();

  

  if (sourceSheet.getName() == sourceSheetName) {

    if (value=='Yes') { 

      if (targetRow > targetSheet.getMaxRows()) targetSheet.insertRowAfter(targetRow - 1);

      sourceSheet.getRange(sourceRow, 1, 1, sourceSheet.getLastColumn()).copyTo(targetSheet.getRange(targetRow, 1));

      targetSheet.getRange(targetRow, sourceSheet.getLastColumn()+1).setValue(new Date());

      sourceSheet.deleteRow(sourceRow);

    };

  };

};

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 Group By Query based on Date condition

Question:

( by Jeff Wasson )

I am using Google Forms to collect information.

It is being dumped into a response Sheets:

From the Response, I have a scorecard that I want to auto populate from the "response" tab. 

It needs to be able to automatically populate once I change the date.

SUMIFS would work in Excel, but I can't seem to figure out a clean way to do that in Sheets.

Any help would be appreciated.

Solution:

Have a look at the following screenshot of sheet 'Scorecard':

And I have the following formula in Cell B3:

=query('Response'!A:G;"select B,sum(C),sum(D),sum(E),sum(F),sum(G) where B<>'' and toDate(A)= date '"&text(B2;"yyyy-MM-dd")&"' group by B label sum(C) 'Add Type',sum(D) 'Upgrade Type',sum(E) 'Option 1',sum(F) 'Option 2',sum(G) 'Accessory' ";1)

So now you have a single formula solution, and when you will update the date in cell B2, the data will update on sheet 'Scorecard'

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