Google-sheets – Make function only work on first sheet, not in whole Google Sheets file

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I have the following code which is for tracking the date of any edit made in a particular row. However, this code applies to the whole spreadsheet file, I would like to change this to be for the 1st sheet only which is titled "Deals List". Can anyone help please?

function onEdit() {
var dateColNum = 7 //column G
var ss1 = SpreadsheetApp.getActiveSpreadsheet();

//set date in same row as edit happens, at fixed column  
ss1.getActiveSheet().getRange(ss1.getActiveRange().getLastRow(), dateColNum, 1, 1).setValue(new Date())
}

Best Answer

Add something like the following

If by "first sheet" you mean one specific sheet no matter the sheets order

if(ss1.getSheetName() == 'Sheet1'){
  //do something like set date
}

If by "first sheet" you mean that the sheets' order matters

if(SpreadsheetApp.getSheets()[0].getSheetName() === ss1.getSheetName()){
  //do something like set date
}

Related Solutions

Google Sheets – Automatically Fill Missing Values

I added this formula to your spreadsheet

=ArrayFormula(if(row(B2:B) <= max(if(not(isblank(B2:B)), row(B2:B))),vlookup(row(B2:B),filter({row(B2:B),B2:B},len(B2:B)),2),))

It seems to deliver what you asked, except for the interpolation part. Enter this formula in row 2, no need to drag down. I hope this helps ?

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Best Answer

Related Solutions

Google Sheets – Automatically Fill Missing Values

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

Related Topic