Need script to loop through stock ticker list, copy resulting output, and paste the output of multip-CodePudding

My question is similar to Need script to loop through stock ticker list, copy resulting output, and paste output to separate sheet within workbook. However, instead of copying and pasting a single row (for each ticker) below the last non-empty row, I need to copy and paste 100 rows (for each ticker) below the last non-empty row.

I have three sheets named Tickers , Data, and Results within a single workbook.

If done manually, the job would be the following: Enter a stock symbol into cell A2 in the Data sheet. The sheet then retrieves the historical time series data from google finance, runs formulas and returns 100 rows and 7 columns of data (Ticker, Date, Open, High, Low, Close, Volume) into range A5:G254 within the same Data sheet. Copy the 100 rows of returned data and paste the data on the Results sheet below the last non-empty row. Then repeat the process with each ticker.

Request: Create a script to loop through a list of 50 stock symbols from the Ticker sheet (range B2:B51), paste 1 symbol at a time into cell A2 in the Data sheet, wait for google finance API to run, and then copy all the resulting rows generated in range A5:G254, and pasting those results into the Results sheet, below the data generated from previous ticker, until there is a "long format" table of time series data of each of the 50 tickers.

The issue with my current script is that it pastes only the first row of the wanted range A5:G254.

function getNewPrices() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tickerSheet = ss.getSheetByName('Tickers');
  const dataSheet = ss.getSheetByName('Data');
  const resultsSheet = ss.getSheetByName('Results');
  let data;


  let myListOfTickers = tickerSheet.getRange('B2:B51').getValues().filter(String).sort();

  myListOfTickers.forEach(ticker => {
    dataSheet.getRange('A2').setValue(ticker[0]);
    data = dataSheet.getRange('A5:G254').getValues()[0];
    pasteData(resultsSheet, data);
  });
};

function pasteData(resultsSheet,data){
  let nextRow = resultsSheet.getLastRow()   1;

  data.forEach((datum,index) => {
    resultsSheet.getRange(nextRow,index   1,1,1).setValue(datum);
  });
};

CodePudding user response：

I'm not completely sure but I think this is what you want

function getNewPrices() {
  const ss = SpreadsheetApp.getActive();
  const tsh = ss.getSheetByName('Tickers');
  let tvs = tsh.getRange('B2:B51').getValues().filter(String).sort();
  const dsh = ss.getSheetByName('Data');
  const rsh = ss.getSheetByName('Results');
  dsh.getRange(2,1,tvs.length,tvs[0].length).setValues(tvs);
  let dvs = dsh.dsh.getRange('A5:G254').getValues();
  rsh.getRange(rsh.getLastRow()   1, 1, dvs.length, dvs[0].length).setValues(dvs);
}

You can goto to Google Apps Script Reference and using the search box find any function that you don't understand. If it's a pure JavaScript function the go here

CodePudding user response：

Problem solved. Here is the final code if anyone wants to use it later on. Thank you for the initial code Cooper.

function getNewPrices() {
  const ss = SpreadsheetApp.getActive();
  const tsh = ss.getSheetByName('Tickers');
  let tvs = tsh.getRange('B2:B51').getValues().filter(String).sort();
  const dsh = ss.getSheetByName('Data');
  const rsh = ss.getSheetByName('Results');
  
  tvs.forEach(ticker =>{
    dsh.getRange('A2').setValue(ticker[0]);
    let dvs = dsh.getRange('A5:G254').getValues();
    rsh.getRange(rsh.getLastRow()   1, 1, dvs.length, dvs[0].length).setValues(dvs);  
  });
}