google-sheetsgoogle-apps-scriptgoogle-forms

Protecting the formula, move it to another cell and keep output in original cell?

I need to approach this a different way. My sheet has date fields for selecting a range of dates (A2 and B2). This works exactly as I need, I can pull a list of trips for any range of dates in the list of field trips.

My problem is that the script to duplicate trips overwrites all the data rows with the new data, so my formula is lost. I need the formula to stay for the next time I need to pull a trip schedule.

I think the solution is to move the formula to a different row. The script skips the first three rows. How can I move the formula into, let's say D1, but have the formula output to cell A4?

Here is my spreadsheet. Look at the sheet called Trip Schedule. I need the date pickers (cell A2 and B2) to choose the selection of trips (this already works), then I run the duplicate trips script, then take that list of results and build a trip schedule document.

function duplicateTrips() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trip Schedule")
  var data = sheet.getRange(4,1,sheet.getLastRow(), sheet.getLastColumn()).getValues();
  var newData = [];
  for(var n in data){
    newData.push(data[n]);
    if(!Number(data[n][2])){continue};// if column 3 is not a number then do nothing
    for(var c=1 ; c < Number(data[n][2]) ; c++){ // start from 1 instead of 0 because we     have already 1 copy
      newData.push(data[n]);//store values
    }
  }
  sheet.getRange(4,1,newData.length,newData[0].length).setValues(newData);// write new data to sheet, overwriting old data
}
Solution

  • Consider this answer executed by a script:

    function onEdit(e) {

  // Logger.log(JSON.stringify(e)) // DEBUG

  var editedValue=e.value
  var editedSheet = e.range.getSheet().getName()
  var editedRow = e.range.rowStart
  var editedCol = e.range.columnStart
  // Logger.log("DEBUG: edited value = "+editedValue+", edited sheet = "+editedSheet+", edited row = "+editedRow+", edited Column = "+editedCol)

  var sheet2Watch = "Trip SchedulebyScript"
  // Logger.log("DEBUG: edited sheet = "+editedSheet+", sheet to watch = "+sheet2Watch+", edited value = "+editedValue)

  // test for sheet and edited value
  if (editedSheet != sheet2Watch || editedValue != "TRUE") {
    // no match, do nothing
    // Logger.log("DEBUG: no match, do nothing")
    return
  }
  // Logger.log("DEBUG: match = build trip schedule")
  createTrips()
}
function createTrips() {

  var tripSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trip SchedulebyScript")
  
  // clear existing data from tripSheet
  var clearTripRange = tripSheet.getRange(4,1,tripSheet.getLastRow()+1,tripSheet.getLastColumn()).clear({contentsOnly: true});
  Logger.log("DEBUG: cleared existing data")

  // get the user variables
  var startDate = tripSheet.getRange("A1").getValue()
  var endDate = tripSheet.getRange("B1").getValue()
  // Logger.log("DEBUG: Start date = "+startDate+"\nEnd date = "+endDate)
  // Logger.log("DEBUG: value of Start date = "+startDate.valueOf())

  // get the data
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data")
  var data = dataSheet.getDataRange().getValues()

  // convert the date column of the data into a 1d array for analysis
  var dates = data.map(function(e){return e[0]})
  // Logger.log(dates) // DEBUG

  // create a temporary Array to hold the data rows
  var tripArray = new Array



  // loop through dates to find matches of the Start and End dates
  for (var i=0;i<dates.length;i++){
    if ((dates[i].valueOf() >= startDate.valueOf()) && (dates[i].valueOf() <= endDate.valueOf())){
      // Logger.log("DEBUG: row "+(i+1)" is a match: "+startDate+"="+dates[i])

      // duplicate the rows as required
      for (var b=0;b<data[i][2];b++ ){
        tripArray.push(data[i])
      }
    }
  }
  // Logger.log("DEBUG: number of trip rows = "+tripArray.length+", number of trip columns = "+tripArray[0].length)

  // paste triparray into the spreadsheet
  tripSheet.getRange(4,1,tripArray.length,tripArray[0].length).setValues(tripArray)
  Logger.log("DEBUG: updated Trip Schedule sheet")

}

    SAMPLE - Checkbox

    checkbox

    ADDENDUM - "Delete" rows instead of "clear" rows

    The OP asks how to "delete the rows of data rather than "clear" them. The process is slightly more complicated.

    The "sheet" method deleteRows(rowPosition, howMany) replaces the "range" method clear(options). This should be straightforward but a couple of issues were noted during testing.

    In function createTrips():

    Remove these lines

    // clear existing data from tripSheet
  var clearTripRange = tripSheet.getRange(4,1,tripSheet.getLastRow()+1,tripSheet.getLastColumn()).clear({contentsOnly: true});
  Logger.log("DEBUG: cleared existing data")

    Substitute these lines

    // delete existing rows of data from tripSheet
  // get the number of rows of data - include a test for no rows
  var numOfRowsData = +tripSheet.getLastRow()-4+1
  if (numOfRowsData <1 ){
    numOfRowsData = 1
  }
  Logger.log("last row = "+tripSheet.getLastRow()+", number of rows = "+numOfRowsData+", columns = "+tripSheet.getLastColumn())
  // delete the rows
  tripSheet.deleteRows(4, numOfRowsData)
  Logger.log("DEBUG: deleted the rows")

    Amend this line

    tripSheet.getRange(4,1,tripArray.length,tripArray[0].length).setValues(tripArray).setFontWeight("normal");