Google Apps Script copy from cell to new row

0
votes

I have two sheets in My Google Spreadsheet.

  1. Master Tracking
  2. Daily Tracking

Master Tracking pulls in information from other tracking sheets and updates the number of miles driven by each driver in cell M7.

I want to set up Daily Tracking with Date (Column A) and Number of Miles Traveled (Column B).

I want to write a Google Apps script which will copy the data from Master Tracking!M7 to Daily Tracking (column B) but into a NEW ROW every time the script is run. I'm planning on using a trigger for it to run at the end of every work day.

I have this till now but it doesn't work.

function copyFunction() {

  var inputRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master Tracking").getRange("M7:M7");

  var inputValues = inputRange.getValues();

  var outputRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily Tracking").getRange("B:B").getLastRow();

  outputRange.setValue(inputValues);
}

I can't figure it out. I can get it to paste in one specific cell but I can't figure out how to paste it into a new row. Maybe getLastRow would work. I don't know.

Also, how to have a timestamp put in Column A of Daily Tracking?

Please help!

1
google-apps-script

1 Answers

1
votes

Didn't test it but should work : (see comments in code)

function copyFunction() {
  var inputRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master Tracking").getRange("M7");
  var inputValue = inputRange.getValue();// use simple getValue without S, get a simple value
  var last = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily Tracking").getLastRow();// get the last row on this sheet
  var outputRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily Tracking").getRange(last+1,1,1,2);// getRange col A & B +1 after last row
  outputRange.setValues([[Utilities.formatDate(new Date(), Session.getTimeZone(), 'MM-dd-yyyy'),inputValue]]);// date string in col A and value in col B in a 2D array (note the S in setValues) - 
}