CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-finance
Scott Fitzpatrick
Scott Fitzpatrick

Reputation: 41

Writing one column of an Array or transposing a 2 column array to a one column then writing

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.

Upvotes: 0

Views: 55

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

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);

Upvotes: 1

Related Questions