Google Spreadsheet Script to convert an Array into a Martix

Question:

( by Francois Jooste )

Hello Everyone,

Im working on a interactive map of customer installations. I need to increase the Latitude and Longitude by .15 of a decimal point, so the map wont show exact customer residential information.

I don't know much about formulas, but there will be about 5,000 cells of data to alter.

Here is a test doc for help:

Thanks in advanced!

Solution:

Have a look at the following screenshot:

I have the following formula in Cell C7:

=k(A2:A22)

the above formula is a custom function k() that I have written and inserted in Script.

Have a look at the following code:

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



function k() {
  var values = arguments[0], returnvalues = new Array();
  for(var i=0;i<(values.length)/7;i++) {
    returnvalues[i] = new Array();
    for(var j=0;j<7;j++) returnvalues[i].push([values[(i*7)+j]]);
  }
  return returnvalues;
}

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

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet or you can use its functionality in the Google Apps Script.

Now, insert the range in function k(), like:
=k(A2:A100) 
OR
=k(D3:D200)

and then you will get the matrix of this array of format ( 7 X N ).
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