Get Google Sheets script to repeat the same steps automatically

user_14933919 0
Viewed 434

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);
};
google-apps-script google-sheets
2 Answers

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

user_147798040

Explanation:

I am confident that your approach can be further optimized. The calculation of Q11 can be somehow changed to incorporate the values of all relevant rows/columns and therefore use single getRange and getValue/setValue calls.

However, I will respect your logic since I can't see your file, but keep in mind that the following approach is not generally recommended and it is going to be slow for large number of data.

  • The first step is to iterate over the rows of your sheet ("Sheet1" - changed that to the name of your sheet) until the last row with content. Make sure that matches the last row of content of column X.

For every row:

  • set the value of T with the value of X.
  • set the value of Y with the value of Q11.

It is a good idea to use flush to apply all the pending changes as this script is going to iteratively interact with the sheet.

Solution:

function myFunction(){
  const spreadsheet = SpreadsheetApp.getActive();
  const sheet = spreadsheet.getSheetByName('Sheet1') // change it to the name of your sheet
  const lrow = sheet.getLastRow();  
  [...Array(lrow)].forEach((_,i)=>{                          
       sheet.getRange(`T${i+1}`).setValue(sheet.getRange(`X${i+1}`).getValue());
       sheet.getRange(`Y${i+1}`).setValue(sheet.getRange('Q11').getValue()); 
       SpreadsheetApp.flush();
  });
}
user_112252910
Related