I'm getting some extra date and time info in Column A (starting with row 2) that I need to remove because it is preventing some Duration calculations. All the rows in Column A are getting the 'GMT 0800 (Pacific Daylight Time)' appended after the actual Day and Time.

So what I'm trying to do with the following code is remove all the text after the space just before 'GMT' (so there's no trailing space remaining afterward). I'm getting a circular dependency error, and understandably so since I'm inserting the formula into the cell that contains the data. How can I avoid this and just remove that pesky GMT stuff from every row in column A? Thanks in advance!

Here's an example of what I'm trying to accomplish:

I have this date and time in a cell:

Nov. 27 2021 19:00:05 GMT 0800 (Pacific Daylight Savings)

And I want it to become:

Nov. 27 2021 19:00:05

My code is currently:

function RemoveGMT() {

// Get Source Spreadsheet
var source = SpreadsheetApp.getActiveSpreadsheet();

// Get Source Sheet from Spreadsheet
var source_sheet = source.getActiveSheet();

// Get Last Row
var lastRow = source_sheet.getLastRow();

// Insert TRIM Formula Into Every Row Of Column A
source_sheet.getRange("A2").setFormula('=TRIM(LEFT(A2,FIND("GMT",A2)))');
var FillDownRange = source_sheet.getRange(2,1,lastRow);
source_sheet.getRange("A2").copyTo(FillDownRange);

}

CodePudding user response：

I believe your goal is as follows.

• You want to convert GMT 0800 (Pacific Daylight Time) to 0800 (Pacific Daylight Time) in the column "A".

If my understanding is correct, how about the following modified script?

Modified script:

function RemoveGMT() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var source_sheet = source.getActiveSheet();
  source_sheet.getRange("A2:A"   source_sheet.getLastRow()).createTextFinder("GMT ").replaceAllWith("");
}

• In this modification, GMT 0800 (Pacific Daylight Time) is converted to 0800 (Pacific Daylight Time). When your values includes GMT 0800 (Pacific Daylight Time) and GMT0800 (Pacific Daylight Time), please modify the last line as follows.

    source_sheet.getRange("A2:A"   source_sheet.getLastRow()).createTextFinder("^GMT ?").useRegularExpression(true).replaceAllWith("");
  

Reference:

• createTextFinder(findText)

CodePudding user response：

Here's my code, I'm just searching for the whole GMT part of the string and replacing it with "".

function RemoveGMT(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dell")
  var lastRow = sheet.getLastRow()
  var lastColumn = sheet.getLastColumn()
  var range = sheet.getRange(1, 1, lastRow)
  var to_replace = "GMT -0800 (Pacific Standard Time)";
  var replace_with = "";
  var data  = range.getValues();
 
    var oldValue="";
    var newValue="";
    var cellsChanged = 0;
 
    for (var r=0; r<data.length; r  ) {
      for (var i=0; i<data[r].length; i  ) {
        oldValue = data[r][i];
        newValue = data[r][i].toString().replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged  ;
          data[r][i] = newValue;
        }
      }
    }
    range.setValues(data);
}