Guest User

Untitled

a guest
Nov 23rd, 2017
65
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.20 KB | None | 0 0
  1. function createHistory() {
  2. var ss = SpreadsheetApp.getActiveSpreadsheet();
  3.  
  4. var sheet1 = ss.getSheetByName("Acciones"); //Get Values
  5. var sheet2 = ss.getSheetByName("Histórico2"); //Write Historic
  6.  
  7.  
  8. var source = sheet1.getRange("C2:C67"); //Header
  9. var values = source.getValues();
  10. var result = new Array(source.getNumRows()+2);
  11. result[0] = "";
  12. for(var x=0; x < source.getNumRows(); x++){
  13. result[x+1]=values[x].toString()
  14. }
  15. result[source.getNumRows()+2]='Total';
  16. sheet2.appendRow(result);
  17.  
  18. Logger.log('Header Saved Successfully');
  19.  
  20. var source2 = sheet1.getRange("A2:AB67"); //Get all values form the sheet
  21. var values2 = source2.getValues();
  22.  
  23.  
  24. var date = new Date(2017,06,20);
  25.  
  26. var newLine = new Array (source2.getNumRows()+2);
  27. newLine[0] = date;
  28.  
  29. Logger.log('JS generated date:' + date);
  30.  
  31. for (var i in values2){ //For each value
  32. Logger.log('This is the value number: ' + i);
  33. if(new Date(values2[i][0]).getTime() <= date.getTime()){
  34.  
  35. var ticker = values2[i][3];
  36. var market = values2[i][4];
  37. var shares = values2[i][5];
  38. var name = values2[i][2];
  39. var price = values2[i][6];
  40. var costs = values2[i][10] + values2[i][10] + values2[i][12];
  41. var xchange_buy = values2[i][14];
  42. var xchange_sell = values2[i][16];
  43. var sold = values2[i][25];
  44.  
  45. Logger.log(' Ticker: ' + ticker + ' Market: ' + market + ' Shares:' + shares + ' Name: ' + name + ' Price: ' + price + ' Costs: ' + costs + ' Sold: ' + sold);
  46.  
  47. var date_formated = Utilities.formatDate(date, "GMT+1:00", "yyyy-MM-dd");
  48. debugger;
  49. var stockPrice = '=' + shares +'* INDEX(GoogleFinance("'+ market + ':' + ticker + '"; "price";"' + date_formated + '"; TODAY());2;2)';
  50. newLine[i+1] = ""+ stockPrice;
  51. Logger.log('The vale that will be stored is: ' + stockPrice);
  52. }
  53. else {
  54. newLine[i+1]=0;
  55. var ticker = values2[i][3];
  56. var market = values2[i][4];
  57. var newdate = new Date(values2[i][0])
  58. Logger.log('The stock value: ' + ticker + ':' + market + ' is not bought till '+ newdate + '.' );
  59. }
  60. }
  61.  
  62. Logger.log('Lets print the end result:');
  63. for (var i in newLine){
  64. Logger.log(newLine[i]);
  65. }
  66.  
  67. sheet2.appendRow(newLine);
  68.  
  69. //}
  70. };
Add Comment
Please, Sign In to add comment