Get Google Sheets script to repeat the same steps automatically

Question

I am brand new to scripting.

I have a set of numbers in column X that I would like to (one at a time) have copied into cell T1, which will then return a result on Q11. I then want it to copy the result in Q11 and paste it on column Y next to the number originally copied from X

I have managed to start the below 2 different options, how do I get it to keep repeating the steps for every row of column X and Y?

Option 1:

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('T1').activate();
  spreadsheet.getCurrentCell().setFormula('=X1');
  spreadsheet.getRange('Y1').activate();
  spreadsheet.getRange('Q11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Option 2:

function myFunction2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('T1').activate();
  spreadsheet.getRange('X1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('Y1').activate();
  spreadsheet.getRange('Q11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Answer 1

If Q11 is a simple function that only uses T1 as its input, it would be better to stick to formulas. You can probably use ARRAYFORMULA (see docs) in the first cell to apply it to the entire column (so it’s automatically applied to the entire column).

If that cannot be done or what you want is to practice using Google Apps Script, you can use a simple loop together with getValue and setValue to achieve it:

function computeData() {
 const sheet = SpreadsheetApp.getActiveSheet()
 const inputRange = sheet.getRange('T1')
 const resultRange = sheet.getRange('Q11')
 
 for (let i = 1; i <= sheet.getLastRow(); i++) {
   const xRange = sheet.getRange(`X${i}`)
   const yRange = sheet.getRange(`Y${i}`)
 
   const x = xRange.getValue()
   inputRange.setValue(x)
 
   const result = resultRange.getValue()
   yRange.setValue(result)
 }
}

It’s worth noting that this script is slow and if you can make the algorithm in the script, it’s better to use getValues of all the values in the column, compute them all and finally do setValues to the other column.

Note: The example script requires the V8 runtime (currently the default one). If the Apps Script project is old, make sure that it’s properly set up.

References

• ARRAYFORMULA (Docs Editors Help)
• SpreadsheetApp getActiveSheet() (Apps Script Reference)
• Sheet getRange(a1Notation) (Apps Script Reference)
• Range getValue() (Apps Script Reference)
• Range setValue(value) (Apps Script Reference)
• Range getValues() (Apps Script Reference)
• Range setValues(values) (Apps Script Reference)
• JavaScript For Loop (W3Schools)