Google-sheets – Setting values in a range of cells using for loop

google sheetsgoogle-apps-script

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.

Best Answer

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).

Code

var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActive();

function onOpen() {
  app.createMenu("Ranges")
    .addItem("Set ranges", "setRanges")
    .addItem("Delete ranges", "deleteRanges")
    .addToUi();
}

function setRanges() {
  var text = app.prompt("Give string", "Like A1:A10, Sheet2!A4:B5, This is a test!A:A", app.ButtonSet.OK_CANCEL); 
  if(text.getSelectedButton() == app.Button.OK) {
    var ranges = text.getResponseText().split(",");  

    for(var i = 0, iLen = ranges.length; i < iLen; i++) {
      var value, range, index, sname, split, sh;
      value = ranges[i].trim(), split = value.split("!");

      switch(split.length) {
        case 1:
          range = value;
          sh = ss.getActiveSheet();
          break;
        case 2:
          range = value;
          sh = ss.getSheetByName(String(split[0].trim()));
          break;
        default:
          index = value.lastIndexOf("!");
          range = "'" + value.slice(0, index) + "'!" + split[split.length - 1].trim();
          sh = ss.getSheetByName(String(value.slice(0, index)));
          break; 
      }

      try {
        sh.getRange(String(range))
          .protect()
          .setDescription("Range" + i);
      } catch(e) {
        throw "Range: " + (i) + " doesn't exist";
      }
    }
  }
}

function deleteRanges() {
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var j = 0, jLen = protections.length; j < jLen; j++) {
    protections[j].remove();
  }
}

Explained

The first two variables are usable throughout the code. The onOpen() function creates a menu item upon file opening and adds two items. The setRanges() function initially creates a popup box (prompt) to capture the string. The response is then split using the comma as delimiter.

Now we need to iterate though the different ranges in order to prepare the protected ranges. Within the iteration, the response is validated once more by splitting it using the exclamation mark as delimiter. This will yield either one or more results. The switch method will determine the buildup of the sheet name and the range.

Now that the sheet has been defined, the range (getRange()) can be protected (protect()) and the try / catch allows for some error handling.

Screenshot

enter image description here

Note

This script can also cope with the following sheet name examples:

This is a test
Test!Test

Example

I've created an example file for you: setting up multiple protected ranges
Add this script in the script editor (Tools > Script editor) and press the bug button: enter image description here
Now you can use the script.

Google-sheets – Get unique values from range of cells

In Google Spreadsheets, you can use the UNIQUE() formula to do that.

Formula

=UNIQUE(A1:A10)

Explained

For the range as seen in the screenshot, there are 10 entries. The UNIQUE() formula accepts a range and filters out the duplicates and returns that range, see screenshot, leaving only 7 unique entries.

Screenshot

enter image description here

Reference

Google Spreadsheets Help: UNIQUE()

Best Answer

Related Solutions

Google-sheets – Setting multiple protected ranges with one range value inputted

Code

Explained

Screenshot

Note

Example

Google-sheets – Get unique values from range of cells

Formula

Explained

Screenshot

Reference

Related Topic