0

Hello I have tried to figure this out for many days and I just can't. Can I lean on you guys for some help?

I am pulling 2 columns from a sheet into an array, then I am looping the array overwriting the second column of the array to become a SQL insert string. Then I write the array to another sheet. The trouble is I really only want to write column 2 of the array to the new sheet. The first column is not needed and it actually occupies cells I need because the price history I am pulling puts the google sheet over the 5,000,000 cell limit. If I can write only one column it won't go over the limit.

//this code is running in a loop returning each different stock in a list of tickers

//get 2 columns from the price history and put them in an array
  const PRICERANGE=SheetPriceHistory.getDataRange();
  var PRICEARRAY=PRICERANGE.getValues();

//append the output of the the entire array to the other sheet.  
SheetScript.getRange(SheetScript.getLastRow()+1,1,PRICEARRAY.length,PRICEARRAY[0].length).setValues(PRICEARRAY); 
// this is where I would like to write just column 2 of the array to the SheetScript sheet.
Scott Fitzpatrick
  • 41
  • 4
  • What is your array of which you want only the first column? PRICEARRAY? – ziganotschka Jul 07 '20 at 11:01
  • Yes PRICEARRAY. PRICEARRAY has 2 columns PRICEARRAY[0][0] and PRICEARRAY[0][1]. I only want to write PRICEARRAY[0][1] to SheetScript and I am trying to do it in one shoot so I can minimize read writes. Is there a way to SwtValues(PRICEARRAY[0][1])? I just don't know what features to use or syntax. – Scott Fitzpatrick Jul 07 '20 at 13:35

1 Answers1

1

Provided that your data is located in the columns A and B of the sheet SheetPriceHistory, you can retrieve the first column selectively

To do so, replace the request getDataRange() by getRange(row, column, numRows, numColumns)

Thereby numColumns will be 1 if you want to retrieve only one column and the number of rows can be retrieved with getLastRow().

So:

 const PRICERANGE=SheetPriceHistory.getRange(1, 1, SheetPriceHistory.getLastRow(), 1);
  var PRICEARRAY=PRICERANGE.getValues();

//append the output of the the entire array to the other sheet.  
SheetScript.getRange(SheetScript.getLastRow()+1,1,PRICEARRAY.length,PRICEARRAY[0].length).setValues(PRICEARRAY);

UPDATE

If you want to retrieve a two column array and drop the first column later on, you can do it with map().

Sample:

  const PRICERANGE=SheetPriceHistory.getRange(1, 1, SheetPriceHistory.getLastRow(), 2);
  var PRICEARRAY=PRICERANGE.getValues();
  ... //do something
  //now overwrite the array with only the second column
  PRICEARRAY = PRICEARRAY.map(function (e) { 
    return [e[1]]; 
  })
  //append the output of the the entire array to the other sheet.  
  SheetScript.getRange(SheetScript.getLastRow()+1,1,PRICEARRAY.length,PRICEARRAY[0].length).setValues(PRICEARRAY);
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • Thank you that would work but there is just one issue. I am using both column A and B to build the SQL string but then I only need to post the column with the insert string to SheetScript. Is there a way to do what you are proposing on the output instead of the input like SetValues(PriceArray[1]) – Scott Fitzpatrick Jul 07 '20 at 13:33
  • I am not quite sure what you mean with SQL string and insert string. If you build this string before assigning the values to `SheetScript` - can you show the relevant code and the structure of the string? – ziganotschka Jul 07 '20 at 13:39
  • Sure this is the looped function here. Its a 2 column array that is pulled from the first sheet then I loop it and overwrite column 2 with the SQL insert string. After this is done I don't need column 1 anymore but I don't know how to write the array to SheetScript without writing both columns. ``` PRICEARRAY[i][1]="INSERT INTO PRICE_HISTORY VALUES ('" + PRICEDATE + "','" + (TICKERARRAY[TICKERCOUNTER][0]) + "','" + String(PRICEARRAY[i][1]) + "')"; ``` – Scott Fitzpatrick Jul 07 '20 at 21:50
  • 1
    Nice I am totally going to try this I will let you know. I did not know about map that looks useful – Scott Fitzpatrick Jul 08 '20 at 14:55
  • 1
    This totally worked I had to adjust the code to get through some syntax errors that I couldn't follow with my limited understanding of the script syntax but here is my final code using your map suggestion. ``` const start = SheetScript.getLastRow()+1 const length = SheetScript.getLastRow()+1 + PRICEARRAY2.length-1 const range = ["A" + start.toString() + ":A" + length.toString()].join(""); const fn = function(v) { return [ v ]; }; SheetScript.getRange(range).setValues(values.map(fn)); ``` I don't even really understand what the code is doing but its working :) – Scott Fitzpatrick Jul 08 '20 at 17:12