Google-sheets – Google Sheets Script Editor action not applying to all sheets

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I have a Sheets file with a tab/sheet for each month. I am editing the Script Editor to apply an automatic Timestamp when an item is scanned. How do I make the same script for each sheet, but make it that it only adds the timestamp to that specific sheet I'm working in and not all sheets?

Here's what I have. I have a file currently for each month, but when scanned in March, it goes to all months.

function onEdit(event)
{
  var timezone = "GMT-5";
  var timestamp_format = "MM-dd—yyyy"; // Timestamp Format.
  var updateColName = "ITEM";
  var timeStampColName = "DATE";
  var sheet = event.source.getSheetByName('MARCH'); //Name of the sheet where you want to run this script.  

  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself! 
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}

Best Answer

Get the sheet that was edited. Change code to:

var sheet, ss;

ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getActiveSheet();//Get the active sheet

Now the sheet variable is the active sheet that was just edited. So anything done with sheet will affect that sheet.

Related Solutions

Google-sheets – Parsing timestamp in Google Sheets with script

The following piece of code will set the background color of the entire row:

function onFormSubmit(e) {
  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get the minutes and hours of the timestamp (time[0]) 
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    e.range.setBackgroundColor('yellow');
  } else {
    e.range.setBackgroundColor('red'); 
  }  
}

UPDATE
This code will set only the back ground color of the timestamp:

function onFormSubmit(e) {
  // get active spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get row index
  var row = e.range.getRowIndex();

  // get the minutes and hours  
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // set range to timestamp
  var range = sh.setActiveCell("A"+row);

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    range.setBackgroundColor('yellow');
  } else {
    range.setBackgroundColor('red'); 
  }  
}

Add this script, via the script editor, to your spreadsheet and set the trigger:
enter image description here

Here's the form: Test For Web Applications and here's the sheet

Google-sheets – Copying data to another sheet based on the month of a date

You don't need a script: the Filter command (which is different from filters and filter views) does this easily.

Suppose your master sheet is named Master and the closing date is in column B. Then in Sheet "Jan" you would enter

=filter(Master!A:Z, month(Master!B:B) = 1)

Here A:Z are the columns of Master sheet (of course there may be more, you can use A:ZZ, etc). The condition that month of the date in column B is equal to 1 means it's January.

In the sheet named "Feb" you would put

=filter(Master!A:Z, month(Master!B:B) = 2)

and so on. The edits to Master sheet will be immediately reflected in the month sheets.

Best Answer

Related Solutions

Google-sheets – Parsing timestamp in Google Sheets with script

Google-sheets – Copying data to another sheet based on the month of a date

Related Topic