CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
minky_boodle
minky_boodle

Reputation: 311

Define sheet range as being on separate sheet

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);
    }

Upvotes: 0

Views: 460

Answers (2)

OblongMedulla
OblongMedulla

Reputation: 1595

@minky_boodle the triggers can be set by going to: enter image description here

then set it: enter image description here

Upvotes: 1

Jack Brown
Jack Brown

Reputation: 5892

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!

Upvotes: 1

Related Questions