Google Script - Macro to get data from a TABLE A and paste required values to Table B in sequence us-CodePudding

I seek help in finalizing a MACRO script for following task.

Basic requirements is to get data from 'Ref' tab (around 200 symbols) in a sequence to 'Mcontrol'!E3 (this tab imports a table from the Website)

From this table, sorted highest 2 values of Symbol/s is extracted and exported to 'OI EOD' tab, i.e. required value is shown in 'OI EOD'! G5:K5.

Then, copy paste range 'OI EOD'! G5:K5 , to the matched row (symbol match to 'Mcontrol'!E3) in the table below ('OI EOD'!C6:C210).

I seek help in putting forEach condition in the macro. SO that next cycle of copy paste function is completed, for each new value in Cell_1 reference.

Hope ppl take notice of the help request from a novice and give good solution and guidance.

Sheet link is Following https://docs.google.com/spreadsheets/d/1az2kas91KFxHcWhtWDg-g20hNYR-_zEiWPvYs-x42YU/edit?usp=sharing

Macro -


  var wb = SpreadsheetApp.getActive();
  var sh1= wb.getSheetByName('Mcontrol');
  var sh2= wb.getSheetByName('OI EOD');
  var R1 = 3
  var C1 = 3
  var R2 = 3
  var C2 = 2
  var R3 = 3
  var C3 = 5
  var cell_1 = sh1.getRange(R1,C1).getValue();
  var cell_2 = sh1.getRange(R2,C2).getValue();
  var cell_3 = sh1.getRange(R3,C3).getValue();
   
  for(i=1;i<cell_2;i  );
  
  cell_1 = i   1; 

  sh1.getRange(R1,C1).setValue(cell_1);{

    var Range_1 = sh2.getRange(6, 3, 300);
    var row_i = 5   ;              // source row no. for data copy paste
    const Row_1 = row_i;
    const Col_2 = 7;
    const Row_Offet1 = 1;
    const Col_Offet1 = 5;

    var data = Range_1.getValues();
    
    let ABC = cell_3;
    
    let row = 1   data.findIndex(users => {return users[0] == ABC});  

    var row_target = row   row_i ;
    
    const sourceRange = sh2.getRange(Row_1,Col_2,Row_Offet1,Col_Offet1);
    
    var destRange = sh2.getRange(row_target,Col_2,Row_Offet1,Col_Offet1);

    sourceRange.copyTo(destRange,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
    
    for(k=1;k<cell_1.i;k  );   
  

    Logger.log(row_target);
    Logger.log(k) ;
    }
  //NEXT
    
    // cell_1=sh1.getRange(R1, C1);
    // sh1.getRange(R1,C1).setValue(i);
  }
}```

Thanks

Robin

CodePudding user response：

Not sure if understand the task as a whole, so here is a guess.

This way you can get values from some start to some end 'symbols' (or numbers) and put these values in correct rows on the 'OI EOD' sheet:

function main() {
  var start = 1;
  var end = 5;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mcontrol_sheet = ss.getSheetByName('Mcontrol');
  var oi_eod_sheet = ss.getSheetByName('OI EOD');

  // get a list of all Symbols from column 'C' of the sheet 'OI EOD'
  var symbols = oi_eod_sheet.getRange('c6:c').getValues().flat();

  for (let i = start; i <= end; i  ) {

    // set the number i on the sheet 'Mcontrol' into the cell 'C3'
    mcontrol_sheet.getRange('c3').setValue(i);

    // wait to complete all the changes on the spreadsheet
    SpreadsheetApp.flush(); 
  
    // get the Symbol and Values from the range 'C5:K5' of the sheet 'OI EOD'
    var [symbl, _, _, _, ...values] = oi_eod_sheet.getRange('c5:k5').getValues()[0];


    // get the row index for this Symbol on the sheet 'Mcontrol'
    var row = symbols.indexOf(symbl)   6;

    console.log({row}, {symbl}, {values});

    // set the values on the sheet 'OI EOD' on the row with given index
    oi_eod_sheet.getRange('g'   row   ':k'   row).setValues([values]);
  }
}

It's up to you to decide how the start and end values could be defined. It can be values from two cells. Or it can be selected range. Or something else.