Add text to the output of values of an array

Question

I just started to learn Apps Script and I need to add some text to each value of an array. This code just puts the values in another column:

function getData() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet()
      .getSheetByName("Stocks");
  var symbol = sheet1.getRange('A1:A7').getValues();
  sheet1.getRange('C1:C7').setValues(symbol);
}

I want to add some text to the output, like this:

function getData() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet()
      .getSheetByName("Stocks");
  var symbol = sheet1.getRange('A1:A7').getValues();
  sheet1.getRange('C1:C7').setValues(
      '=GOOGLEFINANCE("FRA:' + symbol + ")'
  );
}

But I know that this won't work. How do I add to each value being written?

JSmith · Accepted Answer · 2018-10-07T09:53:04.490

0

use a loop to go through your array of values

function getData() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stocks");
  var symbol = sheet1.getRange('A1:A7').getValues();
  var destRange = sheet1.getRange('C1:C7');

  for (var i = 0; i < symbol.length; i++)
    symbol[i][0] = '=GOOGLEFINANCE("FRA:' + symbol[i][0] + '")');

  destRange.setFormulas(symbol);
}

As range A1:A7 and C1:C7 are of same widht and height you can use back that array to then use setFormulas(array) of course use this method if you only use once symbol array if not then declare an empty array befor your loop as so :

var resultArray = []

then inside your for loop do:

 resultArray.push(['=GOOGLEFINANCE("FRA:' + symbol[i][0] + '")']);

then use outside the for loop setFormulas():

destRange.setFormulas(resultArray);

edited Oct 07 '18 at 09:53

answered Oct 06 '18 at 13:48

JSmith

4,519
4
29
45

This works, thank you so much! Just a tiny mistake I found: "<" is missing in the loop at `i symbol.length`. Thanks again! – Anna Oct 06 '18 at 14:07
@JSmith just throwing in some additional information, you should avoid using .setValue() when dealing with functions. Use .setFormula and .getFormula respectively. Here is the documentation: https://developers.google.com/apps-script/reference/spreadsheet/range#setFormula(String). It has the same performance hit but treats the cell better when it comes to updating the sheet afterwards. – Swordstoo Oct 06 '18 at 18:28
@Swordstoo really?? what's the difference exactly do you have any examples? – JSmith Oct 06 '18 at 18:43
@Jsmith The main difference between the two is how it handles what to do with the data in specific circumstances. Things can change based on a few factors. The first is that when you try to use .getFormula in a cell that doesn't have a formula, it will return an empty string which can be useful for circumventing potential issues. Another use case is that when applying formulas to a cell with .setFormula you can circumvent any potential issues on the spreadsheet treating it as a set value and not a formula. A rare case but it can happen with certain cells that are treated differently – Swordstoo Oct 06 '18 at 22:22
Those differences can be data validation, or how they're set to display and treat values. Using .setFormula circumvents all of these issues and helps make your code more readable. Note however that when using .setFormula or .getFormula the data is stored as a string, and you still have to include an '=' to make the formula be treated as a formula. – Swordstoo Oct 06 '18 at 22:23
1

@Swordstoo Just Edited the post – JSmith Oct 07 '18 at 09:53

Add text to the output of values of an array

1 Answers1