Setting values in a range of cells using for loop

Question

I am playing around with scraping stock fundamentals from a website.The spreadsheet has two sheets : Portfolilo and Backend.I am calling Vlookup() from within the script to look up 'company name' for a particular stock from a particular range in the Backend sheet and wish to write the result using setValues() to the Portfolio sheet . The code follows:

function Scrape() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange("C4:C6"); ///to change in future
  var values = range.getValues();
  var look_up_values = ["Company Name","Sector","Market Cap","PE Ratio","Dividend per Share","Dividend Yield","Dividend Cover","DPS Growth (%)","EPS","EPS Growth (%)","Debt Ratio","Debt Equity Ratio","Cash Equity Ratio"]

  var sheet = ss.getSheetByName('Backend');
  var cell = sheet.getRange("A1");
  var formula_link = '"http://www.sharesmagazine.co.uk/shares/share/';

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

      var stock = String(values[i]);

      if(stock.length==2){
        stock.concat(stock,".");
       }

      var formula_complete ="importhtml(".concat(formula_link.concat(stock,'/fundamentals"'),',"table"',",",1,')');
      cell.setFormula(formula_complete);

      var firstleg = sheet.getRange("A1:B11").getValues();
      var secondleg = sheet.getRange("C1:D11").getValues();

      sheet.getRange("F1:G11").setValues(firstleg);
      sheet.getRange("F12:G22").setValues(secondleg);

      var cell_output = ss.getSheetByName('Portfolio').getRange(i+27,1);
      cell_output.setValue('=VLOOKUP('.concat('"',look_up_values[0],'"',",Backend!F1:G22,",2,",FALSE)"  ));
 }   

}

Now, I know what the mistake is but do not know how to solve it. The issue is that the Vlookup formula in the last line of code looks up the value of interest in the same range Backend!F1:G22 which when the loop is finished will only contain data for the final stock in the array therefore I have three cells in the Portfolio sheet that have the same company name instead of three different ones, one for each cell.

Answer 1

I am calling Vlookup() from within the script to look up 'company name' [and] write the result using setValues().

You can't use vlookup (or concat or other built-in functions) within a script. You can use a script that "emulates" vlookup and there are many examples on webapps as well as StackOverflow. Is there a way to emulate Vlookup in Google Script? is an example.

On a tangent, setValue() accepts a value (or a variable). In your example, you might be better off declaring a variable such as, say, var myValue = <insert code here>; and then updating the target range with cell_output.setValue(myValue).