How to paste values from one sheet to another to last row of specific column

0
votes

A novice on app scripts, but managed to successfully build my own script through much research, but now my script is running into errors. Below is my current code:

  function MyFunction() {
  var sss = SpreadsheetApp.getActive(); 
  var ss = sss.getSheetByName('Daily Sales'); 
  var range = ss.getRange('B8:H83'); 
  var data = range.getValues();
  var OUrange = ss.getRange('K8:Q83'); 
  var OUdata = OUrange.getValues();  
  var ts = sss.getSheetByName('Tracker'); 


  ts.getRange(ts.getLastRow()+1, 1,data.length, data[0].length).setValues(data); 
  ts.getRange(ts.getLastRow()+1, 1,OUdata.length, OUdata[0].length).setValues(OUdata);

}

In the Daily Sales sheet I am copying values from columns B-H and K-Q and pasting them in the last row of the Tracker sheet starting at Column A. The Daily Sales values in Col. K-Q are pasted correctly below the B-H values, so happy with those results.

On the Tracker sheet these values are in Columns A-G. However I have since added formulas in Columns I and J based on the script data and a manual entry in Column H. These formulas are copied down the entire column within the sheet (i.e. row 5000). Since adding these formulas, the script is now pasting values in A5001.

I realize it is because of these formulas, but is there a way to update my script to paste in the last row of column A while maintaining those formulas in Columns I and J? Thanks in advance.

2
javascriptgoogle-apps-scriptgoogle-sheets

2 Answers

0
votes

You could create a helper function that computes the first free row of a given column (NOT a column with formulas)

function MyFunction() {
var sss = SpreadsheetApp.getActive(); 
var ss = sss.getSheetByName('Daily Sales'); 
var range = ss.getRange('B8:H83'); 
var data = range.getValues();
var OUrange = ss.getRange('K8:Q83'); 
var OUdata = OUrange.getValues();  
var ts = sss.getSheetByName('Tracker'); 
var values = data.concat(OUdata).filter(function (r) {return r[0]});
var fr = findFirstFreeRow(ts, 1) // second parameter represents column to check for first free row
ts.getRange(fr, 1,values.length, values[0].length).setValues(values); 
}


function findFirstFreeRow(sheet, colNum) {
var v = sheet.getRange(1, colNum, sheet.getLastRow()).getValues(),
    l = v.length,
    r;
while (l >= 0) {
    if (v[l] && v[l][0].toString().length > 0) {
        r = (l + 2);
        break;
        } else {
        l--;
        }
    }
return r;
}

See if that helps?

0
votes

After deleting the extra empty rows from your 'Tracker' sheet, you can use the appendRow function to create a new row with the manual values (leaving the cells that need formulas blank).

After the manual values are in, you can then get the cells that need formulas and use setFormula on them.

For the sake of brevity, if you wanted column A,B,C,E,F to have manual values and column D to have a formula you would do:

var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['A','B','C','','E','F']);
sheet.getRange(sheet.getLastRow(),4).setFormula('=FORMULA(HERE)');