Google Spreadsheet Script Reverse Loop

Question:

( by tomcon_dupe )

I assume this is easy.  I'm a VBA programmer but new to java and functions in Google Sheet.

Did look through some of the tutorials, but did not find obvious answer, so if somebody could write this out in Java, i can adapt and fill in with my needed custom function's logic.

I want to pass to the function what i'd call a "1-column vector of variable length". Meaning, i'd like to call it like this =MyFunction(A10:A20) or =MyFunction(G23:G99), etc.

In the tutorial examples i could find, it showed passing in a single value for a function like "inches to centimeters" but did not show how the java code handles it if a vector of values instead of a single value is passed in as the argument.

I am then going to iterate through that vector of values, backwards, starting at the last and ending with the first, and compute a single value to return.  I'm sure i can work out how to compute what i need to compute within the loop; just want to see the overall framework, how to get this started. How to pass in the vector, and how to iterate over the values, given that it will not be a fixed number of values, but the vector could be a different length each time the function is called.

If you can help me out by posting what i would imagine is a very brief bit of code, would appreciate it.

Thanks much!  Tom.

Solution:

Have a look at the following screenshot:

I have the following formula in Cell B2:

=kishan(A2:A7)


the above formula is a custom function kishan() that you can use in spreadsheet by inserting the following code in the script editor of your spreadsheet.


Have a look at the following code:

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



function kishan() {
  var values = arguments[0];
  var returnvalues = [];
  for(var i=values.length-1;i>=0;i--)
    returnvalues.push([ values[i] ]);
  return returnvalues;

}

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


the above function =kishan() will take the first argument from the parameters and then it will start the loop in reverse order and insert these values in another array, and finally return those values.


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.


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