Actually I am having a hard time finding the correct group to post this in
anyone have a link for a good group that this would fit into?
Thanks!
On Thursday, December 31, 2015 at 11:42:34 AM UTC-8, Michael M wrote:
>
> Oops, I think this is the wrong forum, Sorry.
>
> On Thursday, December 31, 2015 at 11:39:39 AM UTC-8, Michael M wrote:
>>
>> I update these as I learn how to code better. Some built from scratch,
>> Some borrowed, and Some modified. Please feel free to use, Share, or
>> Comment.
>>
>>
>> //~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
>> //--//Dependent on isEmpty_()
>> // Script Look-up
>> /*
>> Benefit of this script is:
>> -That google sheets will not continually do lookups on data that is not
>> changing with using this function as it is set with hard values until
>> script is kicked off again.
>> -Unlike Vlookup you can have it look at for reference data at any Column
>> in the row. Does not have to be in the first column for it to work like
>> Vlookup.
>> -You can return the Lookup to Memory for further processing by other
>> functions
>>
>> Useage:
>>
>> var LocNum =
>> SpreadsheetApp.openById(SheetID).getSheetByName('Sheet1').getRange('J2:J').getValues();
>>
>> Lookup_(Sheetinfo,"Sheet1!A:B",0,[1],"Sheet1!I1","n","y");
>> //or
>> Lookup_(Sheetinfo,"Sheet1!A:B",0,[1],"return","n","n");
>> //or
>> Lookup_(Sheetinfo,"Sheet1!A:B",0,[0,1],"return","n","n");
>> //or
>> Lookup_(Sheetinfo,"Sheet1!A:B",1,[0],"return","y","n");
>> //or
>> Lookup_(Sheetinfo,"Sheet1!A:G",4,[0],"Database!A1","y","y");
>>
>> -Loads all Locations numbers from J2:J into a variable
>> --looks for Location Numbers in Column 0 of Referance sheet and range eg
>> "Data!A:G"
>> ---Returns results to Column 3 of Target Sheet and range eg "test!A1" or
>> "1,1"
>>
>> */
>>
>>
>>
>> function Lookup_(Search_Key,RefSheetRange,SearchKey_Ref_IndexOffSet,
>> IndexOffSetForReturn,SetSheetRange,ReturnMultiResults,Add_Note)
>> {
>> var RefSheetRange = RefSheetRange.split("!");
>> var Ref_Sheet = RefSheetRange[0];
>> var Ref_Range = RefSheetRange[1];
>>
>> if(!/return/i.test(SetSheetRange))
>> {
>> var SetSheetRange = SetSheetRange.split("!");
>> var Set_Sheet = SetSheetRange[0];
>> var Set_Range = SetSheetRange[1];
>> var RowVal =
>> SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_Range).getRow();
>> var ColVal =
>> SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_Range).getColumn();
>> }
>>
>> var twoDimensionalArray = [];
>> var data =
>> SpreadsheetApp.getActive().getSheetByName(Ref_Sheet).getRange(Ref_Range).getValues();
>>
>> //Syncs sheet by name and range into var
>> for (var i = 0, Il=Search_Key.length; i<Il; i++)
>> // i = number of rows to index and
>> search
>> {
>> var Sending = [];
>> //Making a Blank Array
>> var newArray = [];
>> //Making a Blank Array
>> var Found ="";
>> for (var nn=0, NNL=data.length; nn<NNL; nn++)
>> //nn = will be the number of
>> row that the data is found at
>> {
>> if(Found==1 && ReturnMultiResults.toUpperCase() == 'N')
>>
>> //if statement for found if found = 1 it will to stop all other logic
>> in nn loop from running
>> {
>> break;
>> //Breaking nn loop once found
>> }
>> if (data[nn][SearchKey_Ref_IndexOffSet]==Search_Key[i])
>> //if statement is triggered when the
>> search_key is found.
>> {
>> var newArray = [];
>> for (var cc=0, CCL=IndexOffSetForReturn.length; cc<CCL; cc++)
>> //cc = numbers of columns to
>> referance
>> {
>> var iosr = IndexOffSetForReturn[cc];
>> //Loading the value of current cc
>> var Sending = data[nn][iosr];
>> //Loading data of Level nn offset by
>> value of cc
>> if(isEmpty_(Sending))
>> //if statement for if one of the returned
>> Column level cells are blank
>> {
>> var Sending = "#N/A";
>> //Sets #N/A on all column levels that
>> are blank
>> }
>> if (CCL>1)
>> //if statement for multi-Column returns
>> {
>> newArray.push(Sending);
>> if(CCL-1 == cc)
>> //if statement for pulling all
>> columns into larger array
>> {
>> twoDimensionalArray.push(newArray);
>> var Found = 1;
>> //Modifying found to 1 if found to stop
>> all other logic in nn loop
>> break;
>> //Breaking cc loop once found
>> }
>> }
>> else if (CCL<=1)
>> //if statement for single-Column
>> returns
>> {
>> twoDimensionalArray.push(Sending);
>> var Found = 1;
>> //Modifying found to 1 if found to
>> stop all other logic in nn loop
>> break;
>> //Breaking cc loop once found
>> }
>> }
>> }
>> if(NNL-1==nn && isEmpty_(Sending))
>> //following if statement is for if the
>> current item in lookup array is not found. Nessessary for data structure.
>> {
>> for(var na=0,NAL=IndexOffSetForReturn.length;na<NAL;na++)
>> //looping for the number of columns
>> to place "#N/A" in to preserve data structure
>> {
>> if (NAL<=1)
>> //checks to see if it's a single
>> column return
>> {
>> var Sending = "#N/A";
>> twoDimensionalArray.push(Sending);
>> }
>> else if (NAL>1)
>> //checks to see if it's a Multi
>> column return
>> {
>> var Sending = "#N/A";
>> newArray.push(Sending);
>> }
>> }
>> if (NAL>1)
>> //checks to see if it's a Multi column
>> return
>> {
>> twoDimensionalArray.push(newArray);
>> }
>> }
>> }
>> }
>> if (CCL<=1)
>> //checks to see if it's a single column
>> return for running setValue
>> {
>> var
>> ...
>
>
--
You received this message because you are subscribed to the Google Groups
"Google Spreadsheets API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
