Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function createHistory() {
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var sheet1 = ss.getSheetByName("Acciones"); //Get Values
- var sheet2 = ss.getSheetByName("Histórico2"); //Write Historic
- var source = sheet1.getRange("C2:C67"); //Header
- var values = source.getValues();
- var result = new Array(source.getNumRows()+2);
- result[0] = "";
- for(var x=0; x < source.getNumRows(); x++){
- result[x+1]=values[x].toString()
- }
- result[source.getNumRows()+2]='Total';
- sheet2.appendRow(result);
- Logger.log('Header Saved Successfully');
- var source2 = sheet1.getRange("A2:AB67"); //Get all values form the sheet
- var values2 = source2.getValues();
- var date = new Date(2017,06,20);
- var newLine = new Array (source2.getNumRows()+2);
- newLine[0] = date;
- Logger.log('JS generated date:' + date);
- for (var i in values2){ //For each value
- Logger.log('This is the value number: ' + i);
- if(new Date(values2[i][0]).getTime() <= date.getTime()){
- var ticker = values2[i][3];
- var market = values2[i][4];
- var shares = values2[i][5];
- var name = values2[i][2];
- var price = values2[i][6];
- var costs = values2[i][10] + values2[i][10] + values2[i][12];
- var xchange_buy = values2[i][14];
- var xchange_sell = values2[i][16];
- var sold = values2[i][25];
- Logger.log(' Ticker: ' + ticker + ' Market: ' + market + ' Shares:' + shares + ' Name: ' + name + ' Price: ' + price + ' Costs: ' + costs + ' Sold: ' + sold);
- var date_formated = Utilities.formatDate(date, "GMT+1:00", "yyyy-MM-dd");
- debugger;
- var stockPrice = '=' + shares +'* INDEX(GoogleFinance("'+ market + ':' + ticker + '"; "price";"' + date_formated + '"; TODAY());2;2)';
- newLine[i+1] = ""+ stockPrice;
- Logger.log('The vale that will be stored is: ' + stockPrice);
- }
- else {
- newLine[i+1]=0;
- var ticker = values2[i][3];
- var market = values2[i][4];
- var newdate = new Date(values2[i][0])
- Logger.log('The stock value: ' + ticker + ':' + market + ' is not bought till '+ newdate + '.' );
- }
- }
- Logger.log('Lets print the end result:');
- for (var i in newLine){
- Logger.log(newLine[i]);
- }
- sheet2.appendRow(newLine);
- //}
- };
Add Comment
Please, Sign In to add comment