this script is to run on multiple spreadsheets and copy(export) selected data to 1 central SS

function doExportBLC()
{
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ss_c = ss.getSheetByName('Config');

    var TKT = ss_c.getRange(TKR).getValue();              // TKR = Ticket Range
    var Export = ss_c.getRange(EBL).getDisplayValue();    // EBL = Export to BLC 

    var Target_Id = ss_c.getRange(TDR).getValues();       // TDR = Target ID Range

  const ss_s = ss.getSheetByName('Index');  // Source sheet

    var A = ss_c.getRange('B3').getValue();    // Ticket
    var B = ss_c.getRange('B18').getValue();    // Balanço Atual

    var C = ss_s.getRange('B38').getValue();   // Ativo
    var D = ss_s.getRange('B34').getValue();   // A. Circulante
    var E = ss_s.getRange('B40').getValue();   // A. Não Circulante
    var F = ss_s.getRange('B41').getValue();   // Passivo
    var G = ss_s.getRange('B42').getValue();   // Passivo Circulante
    var H = ss_s.getRange('B43').getValue();   // Passivo Não Circ
    var I = ss_s.getRange('B44').getValue();   // Patrim. Líq

  var Data = [];
  Data.push(A,B,C,D,E,F,G,H,I);

  if( Export == "TRUE" )
  {
    const trg = SpreadsheetApp.openById(Target_Id);   // Target spreadsheet
    const ss_t = trg.getSheetByName('BLC');  // Target sheet

      var LR_T = ss_t.getLastRow();
      var LC_T = ss_t.getLastColumn();

      var search = ss_t.getRange("A2:A"   LR_T).createTextFinder(TKT).findNext();

  //  if (!search) return;
    if (search)
    {
      search.offset(0, 1, 1 , Data.length).setValues(Data);
    }
    else
    {
      var export_data = ss_t.getRange(LR_T 1,1,1,1).setValue([TKT]);
    }
  }
};

Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues. doExportBLC @ temp.gs:44

Line 44: search.offset(0, 1, 1 , Data.length).setValues(Data);

that script is to search if the ticked was already exported, if no fill new ticket and if yes export data to that line

the part, if no and add new ticket works, the rest to export data isnt, everything else i get those variables from global constants used on other scripts and that works

im getting individual cels from more than 1 sheet and various cels with getValue

var A = ss_c.getRange('B3').getValue();    // Ticket

then

  var Data = [];
  Data.push(A,B,C,D,E,F,G,H,I);

to setValues with

      search.offset(0, 1, 1 , Data.length).setValues(Data);

as far as i understand that should be the correct way to deal with a 1 dimension array, as the output should be just 1 row

kinda lost here, help is appreciated, thanks

EDIT: added example [Example]

• Config and Index are from source SS, where function will run, multiple sheets will run this
• BLC is from target SS, where data will be exported to

CodePudding user response：

Without being able to see the spreadsheet I have to guess but perhaps this will help

function doExportBLC() {
  const ss = SpreadsheetApp.getActive();
  const shc = ss.getSheetByName('Config');
  var TKT = shc.getRange(TKR).getValue();              // TKR = Ticket Range
  var Export = shc.getRange(EBL).getDisplayValue();    // EBL = Export to BLC 
  var tid = shc.getRange(TDR).getValues();       // TDR = Target ID Range
  const shs = ss.getSheetByName('Index');  // Source sheet
  var A = shc.getRange('B3').getValue();    // Ticket
  var B = shc.getRange('B18').getValue();    // Balanço Atual
  var C = shs.getRange('B38').getValue();   // Ativo
  var D = shs.getRange('B34').getValue();   // A. Circulante
  var E = shs.getRange('B40').getValue();   // A. Não Circulante
  var F = shs.getRange('B41').getValue();   // Passivo
  var G = shs.getRange('B42').getValue();   // Passivo Circulante
  var H = shs.getRange('B43').getValue();   // Passivo Não Circ
  var I = shs.getRange('B44').getValue();   // Patrim. Líq
  var Data = [];
  Data.push([A, B, C, D, E, F, G, H, I]);
  if (Export == "TRUE") {
    tid.forEach(id => {
      let trg = SpreadsheetApp.openById(tid);   // Target spreadsheet
      let ss_t = trg.getSheetByName('BLC');  // Target sheet
      var LR_T = ss_t.getLastRow();
      var LC_T = ss_t.getLastColumn();
      var search = ss_t.getRange("A2:A"   LR_T).createTextFinder(TKT).findNext();
      if (search) {
        search.offset(0, 1, Data.length, Data[0].length).setValues(Data);
      }
      else {
        var export_data = ss_t.getRange(LR_T   1, 1, 1, 1).setValue(TKT);
      }
    });
  }
}