3

I have built a stock tracking worksheet on Google Sheets where if I enter XYZ symbol in a cell B1, the spreadsheet will pull in Google Finance data (sometimes very slow), do a series of calculations and output an Yes/No in cell B18.

In another sheet, named data, I have a list of 20 stock ticker symbols in cells A1:A20.

I want to write a function to change cell B1 with the information from data!A1:A20, looping 20 times one row at a time. During this process, at each step if the output in B18 is an "Yes", then the function should record the corresponding B1 input on say column L, starting at L2.

Any help, pointers will be sincerely appreciated for this newbie.

2

You need an Apps Script for this (under Tools > Script Editor). Here is how it could work (explanation below).

function loop() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');   // name of your sheet
  var data = ss.getSheetByName('data').getRange('A1:A20').getValues();
  var output = [];
  for (var i = 0; i < data.length; i++) {
    sheet.getRange('B1').setValue(data[i][0]);
    SpreadsheetApp.flush();
    Utilities.sleep(10000);
    if (sheet.getRange('B18').getValue() == 'Yes') {
      output.push([data[i][0]]);
    }
  }
  sheet.getRange(2, 12, output.length, 1).setValues(output);
}

The lines until for are just setting up pointers to resources and grabbing data. In the loop, the value in B1 is set; the spreadsheet is flushed to make sure it really applies; and then the script waits 10 seconds (10000 milliseconds) for the financial data to arrive. You may decide to change this pause depending on your sheet's performance. If the value in B18 is Yes, the stock symbol is added to array "output". After the whole thing has ran, the output is placed in column L (column number 12).


To be able to run this function from the spreadsheet itself, add the function below to the script, which will create a menu item for it.

function onOpen() {
   var menu = [{name: "Run a loop", functionName: "loop"}];
   SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);  
}

(Another option is to use a clickable drawing).

  • 1
    This is awesome. Thanks a ton for all your help. – Tapan Samaddar Jan 8 '16 at 23:41
  • A quick followup. I am running this script from the script editor window and clicking on the Run button. How can I call this loop function from the main tab, Sheet1 itself. I tried by assigning a random cell as =loop. That didn't work. – Tapan Samaddar Jan 9 '16 at 5:56
  • I added an explanation. – user79865 Jan 9 '16 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.