Помощь с инструментом поиска в Google Таблицах

Итак, после обсуждения проблемы с МакШихи, проблема на самом деле вот в чем.

Как получить данные из ОДНОЙ таблицы, нескольких листов по моему выбору. и написать В МНОГИЕ электронные таблицы, на несколько листов в этих таблицах, по моему выбору.

Как только эти данные окажутся в нужном месте, текущие формулы должны работать или их можно будет легко адаптировать.

Я придумал решение сценария. Пользователь создает лист настроек в исходной таблице. В А2 вниз, Целевые ключи электронной таблицы, В2 вниз, Имена исходных листов, которые вы хотите включить из текущего листа. C2 вниз - это имена целевых ЛИСТОВ, если вы хотите записать данные на более чем один лист.

Куски кода снабжены аннотациями, чтобы помочь объяснить вопросы МакШихи о том, как это работает.

Если у кого-то есть какие-либо улучшения, и я уверен, что они есть, в частности, бит заголовков. (это не нужно, но моя строка clearContent / clearConents постоянно срывается), я весь уши.

Спасибо

function getOvenDataV5(){


    var settingsSheetName = "monkey_settings";

    /* DO NOT EDIT BELOW THIS LINE */  


    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var settings = ss.getSheetByName(settingsSheetName);


      // this bit has been edited, note the getValues, not getValue, as we want the whole column now not just a single cell.
    var targetSheetsValues = settings.getRange("C2:C").getValues(); // this gets the target sheet names from the settings sheet
    var targetSheets = []; // And array added to throw target sheet names into, as there is more than one.



      // the reason we use arrays and loops (later on), is because the script has no idea how much data to expect.
      // so we go through whatever it's grabbed, the stuff it thinks is data, but we check it later on.
      // only a simple check. Our check is that it cannot be blank. ""
      // then stuff it in an array, a handy thing to store data, for use later on.



      var sSheets = settings.getRange("B2:B").getValues();
      var sourceSheets = []; 


    // new loop below to get the target sheets names. We'll use this in the write bit later.

        for(var i = 0; i < targetSheetsValues.length;i++){
        if(targetSheetsValues[i][0]!=""){
          targetSheets.push(targetSheetsValues[i]);
        }

      }






      for(var i = 0; i < sSheets.length;i++){
        if(sSheets[i][0]!=""){
          sourceSheets.push(sSheets[i]);
        }

      }


      var dKeys = settings.getRange("A2:A").getValues(); 
      var sKeys = []; 



      for(var i = 0; i < dKeys.length;i++){
        if(dKeys[i][0]!=""){
          sKeys.push(dKeys[i]);
        }

      }

      var data = []; 

      for (var i = 0; i < sourceSheets.length;i++){




        var values = ss.getSheetByName(sourceSheets[i]).getDataRange().getValues(); 



        for (var x = 1;x < values.length; x++){



          if(values[x][0]!= ""){
            data.push(values[x]);
          }


      }




    }






      // Below is an array of your column headers, the script was being annoying when clearing sheet data, so decided to clear the whole damn sheet
      // then write the headers via here instead
      var headers = [["Model No", "Product Code", "Brand", "Model No", "kW", "Amp"]];



    for (var i = 0; i< sKeys.length;i++){
    var tss = SpreadsheetApp.openById(sKeys[i]);
      for(var x = 0; x < targetSheets.length;x++){ // this loop, within the keys loop, goes through the target sheets array
    var target =  tss.getSheetByName(targetSheets[x]); // this loads the target sheet, one by one
    var range = target.getRange(2,1, data.length, data[0].length); // this gets the cells to write to
    target.clearContents(); // clear the sheet before writing data
        target.getRange("A1:F1").setValues(headers); // write the headers to a1:F1 in target sheet

    range.setValues(data); //write the data
      }
    }
    }

Я не знаю, будут ли они иметь какую-то ценность, но вот две вещи, которые я написал в скрипте.

Ниже приведен сценарий vlookup для установки или возврата в память с подсказками о возможностях запроса.

//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//--//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:

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_("cat","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 singleArrayForm = [];
    for (var l = 0,lL=twoDimensionalArray.length; l<lL; l++)                                                          //Builds 2d Looping-Array to allow choosing of columns at a future point
    {
      singleArrayForm.push([twoDimensionalArray[l]]);
    }
    if(!/return/i.test(SetSheetRange))
    {
      SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,singleArrayForm.length,singleArrayForm[0].length).setValues(singleArrayForm);
      SpreadsheetApp.flush();
      if(/y/i.test(Add_Note))
      {
        SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,1,1).setNote("VLookup Script Ran On: " + Utilities.formatDate(new Date(), "PST", "MM-dd-yyyy hh:mm a") + "\nRange: " + Ref_Sheet + "!" + Ref_Range);      
      }
    }
    else
    {
      return singleArrayForm
    }
  }
  if (CCL>1)                                                                                             //checks to see if it's a multi column return for running setValues
  {
    if(!/return/i.test(SetSheetRange))
    {
      SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,twoDimensionalArray.length,twoDimensionalArray[0].length).setValues(twoDimensionalArray);
      SpreadsheetApp.flush();
      if(/y/i.test(Add_Note))
      {
        SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,1,1).setNote("VLookup Script Ran On: " + Utilities.formatDate(new Date(), "PST", "MM-dd-yyyy hh:mm a") + "\nRange: " + Ref_Sheet + "!" + Ref_Range);      
      }
    }
    else
    {
      return twoDimensionalArray
    }
  }

}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`

//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//--//Dependent on isEmpty_()
//Find Last Row on Database
function getFirstEmptyRowUsingArray_(sheetname) 
{
  var data = SpreadsheetApp.getActive().getSheetByName(sheetname).getDataRange().getValues();
  for(var n = data.length ; n>0 ;  n--)
  {
    if(isEmpty_(data[n])==false)
    {
      n++;
      break;
    }
  }
  n++;
  return (n);
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`

Ниже приведен сценарий ImportRange, потому что я ненавижу, как = importrange () требует, чтобы вы «запрашивали доступ» для каждой новой формулы. Если этот диапазон импорта встроен, вы должны вынуть его, запросить доступ и повторно встроить его. Также очень часто формула просто не работает. так:

//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//Script Based ImportRange

//Example importRange_('0AodPsggrbsh1dDNH............','Main4NS','A:G','Common','C7','y')
//Explanation importRange_('Importing Spreadsheet Key or URL','Importing Spreadsheet Tab Name','Importing Spreadsheet Tab's Range','Destination Spreadsheet Tab Name','Destination Spreadsheet Tab's placement','Will add note to the first cell of import')

function importRange_(Source_Key,Source_Sheet,Source_Range,Set_Sheet,Set_Pos,Add_Note) 
{
  var SourceTypeCheck = Source_Key.indexOf("https://"); 
  if(SourceTypeCheck >= 0)
  {
    var Load = SpreadsheetApp.openByUrl(Source_Key).getSheetByName(Source_Sheet).getRange(Source_Range).getValues();
    var Name = SpreadsheetApp.openByUrl(Source_Key).getName();
  }
  if(SourceTypeCheck == -1)
  {
    var Load = SpreadsheetApp.openById(Source_Key).getSheetByName(Source_Sheet).getRange(Source_Range).getValues();
    var Name = SpreadsheetApp.openById(Source_Key).getName();
  }
  var RowVal = SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_Pos).getRow();
  var ColVal = SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_Pos).getColumn();
  if(Add_Note.toUpperCase() == 'Y')
  {
    SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,1,1).setNote("Import Script Updated On: " + Utilities.formatDate(new Date(), "PST", "MM-dd-yyyy hh:mm a")+"\nSS Name: "+Name+"\nRange: "+Source_Sheet+"!"+Source_Range+"\nSS Key: "+ Source_Key);      
  }
  SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,Load.length,Load[0].length).setValues(Load);
  SpreadsheetApp.flush();
  SpreadsheetApp.getActiveSpreadsheet().toast('At: '+Set_Sheet+'!'+Set_Pos,'Import Completed:');
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`

Стоит то же самое. Если у вас есть идеи по улучшению, дайте мне знать. Если вы найдете способ интегрировать его, классные beans, опубликуйте код того, как вы его использовали.

:)