Define sheet range as being on separate sheet

Question

currently using this Google Apps script to populate formulas in same row as edits are made, but I'd like to see it populating cells on a different sheet within the same file.

It seems like I need to define destrange as being the next empty row on a separate destination sheet but I"m not sure how to do that.

function onEdit(e){
var ui = SpreadsheetApp.getUi(); 
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = e.range;
var sheet = range.getSheet()
var newSheet = ss.getSheetByName("Apps")
var sheetName = sheet.getName()
if(sheetName != "Form"){
   return                            //exit function
}  
var columnOfCellEdited = range.getColumn();
if (columnOfCellEdited === 1) 
 var range2 = range.getRow();
 var destrange = newSheet.getLastRow()
 sheet = newSheet
     // adds the formulas
     var cell = sheet.getRange("C" + destrange);
  cell.setFormula('=Form!$B' + destrange);
     var cell = sheet.getRange("D" + destrange);
     cell.setFormula('=Form!$C' + destrange);
     var cell = sheet.getRange("E" + destrange);
     cell.setFormula('=Form!$D' + destrange);
     var cell = sheet.getRange("F" + destrange);
     cell.setFormula('=Form!$E' + destrange);
     var cell = sheet.getRange("G" + destrange);
     cell.setFormula('=Form!$F' + destrange);
     var cell = sheet.getRange("H" + destrange);
     cell.setFormula('=Form!$G' + destrange);
    }

score 1 · Answer 1 · edited May 23 '17 at 10:31

All you need to do is to set the sheet variable to the sheet you want to write in

For example: In the code below:

var sheet = range.getSheet()

to get a sheet named "Sheet2" you would do this

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet2")

To get an empty row on the new sheet, you will do the following

 var destRange = sheet.getLastRow()+1

Document on getSheetByName can be found here

Also note, your approach of editing one cell at a time is not the most efficient approach Have look at this question to understand how to a batch update Google app script timeout ~ 5 minutes?

Edit:

Final code:

function onEdit(e){
    var ui = SpreadsheetApp.getUi(); 
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var range = e.range;
    var sheet = range.getSheet()
    var newSheet = ss.getSheetByName("Sheet2")
    var sheetName = sheet.getName()
    if(sheetName != "Form Responses"){
       return                            //exit function
    }  
    var columnOfCellEdited = range.getColumn();
    if (columnOfCellEdited === 1) 
     var range2 = range.getRow();
     var destrange = newSheet.getLastRow()
     sheet = newSheet
     // adds the formulas
     var cell = sheet.getRange("C" + destrange);
  cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH(":",$B' + destrange +')+1,SEARCH(",",$B' + destrange +')-SEARCH(":",$B' + destrange +')-1),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("D" + destrange);
     cell.setFormula('=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B' + destrange +',FIND("$",B' + destrange +'),LEN(B' + destrange +'))," ",REPT(" ",100)),100)),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("E" + destrange);
     cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("exceed",$B' + destrange +')+7,SEARCH("%",$B' + destrange +')-SEARCH("exceed",$B' + destrange +')-6),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("F" + destrange);
     cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("due",$B' + destrange +')+3,SEARCH(";",$B' + destrange +')-SEARCH("due",$B' + destrange +')-3),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("G" + destrange);
     cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("held on",$B' + destrange +')+7,SEARCH(". Lottery",$B' + destrange +')-SEARCH("held on",$B' + destrange +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("H" + destrange);
     cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("posted by",$B' + destrange +')+9,SEARCH(". ",$B' + destrange +')-SEARCH("",$B' + destrange +')-167),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    }

Hope that helps!

I haven't been having any timeout issues with this script. At least not yet. Thanks for the suggestions. What would the final script look like? — minky_boodle, Apr 13 '17 at 22:23
That doesn't seem to be working. Maybe I need to do some more work on the script. What I'm trying to do is: anytime there's a form submission, transfer data from the new row, taking from Columns B-G in `Form Responses` and sending to the first blank row of `Sheet 2`. — minky_boodle, Apr 13 '17 at 23:54
onedit doesn't trigger when a form response is submitted to the sheet. You will have to use from submit trigger — Jack Brown, Apr 14 '17 at 00:07

score 1 · Answer 2 · answered Apr 14 '17 at 12:35

1

@minky_boodle the triggers can be set by going to:

then set it:

answered Apr 14 '17 at 12:35

OblongMedulla

1,471
9
21

1

Still not quite getting what I'm looking for. Made some edits to original post above, hoping to get this worked out before Monday afternoon deadline – minky_boodle Apr 15 '17 at 07:08

Define sheet range as being on separate sheet

2 Answers2