Replace character using google apps script

Question

I have a column which is a date in string format with this format

2020-02-23T12:14:06+0000

And i want to remove the T and replace it with space and also just completely remove the last part (+0000)

I have tried this

  var A1 = CONTENT.getRange("B:B").getValue();
var A1String = A1.toString().replace("T*", "");

but it doesn't work.

Any ideas?

This is the original script in which i want to incorporate it into.

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('sheetname');
  var range = sheet.getRange("A:C");
  var response = UrlFetchApp.fetch("API CALL");
  var dataAll = JSON.parse(response.getContentText());
  var dataSet = dataAll.data;
  var rows = [],
    data;
  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];
    rows.push([new Date(),data.created_time,data.message,data.permalink_url,
               data.reactions.summary.total_count
               ,data.comments.summary.total_count,data.insights.data[1].values[0].value,data.insights.data[2].values[0].value,data.insights.data[3].values[0].value,data.insights.data[0].values[0].value['link clicks'],data.insights.data[0].values[0].value['photo view'],data.insights.data[0].values[0].value['other clicks'],data.insights.data[0].values[0].value['video play'],data.insights.data[4].values[0].value,data.insights.data[5].values[0].value,data.insights.data[6].values[0].value,data.insights.data[7].values[0].value["like"],data.insights.data[7].values[0].value["love"],data.insights.data[7].values[0].value["wow"],data.insights.data[7].values[0].value["haha"],data.insights.data[7].values[0].value["sorry"]]); //your JSON entities here
  }
  Logger.log(rows)
  //sheet.getRange(getlastRow() + 1, 1, rows.length, 2).setValues(rows);
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 22).setValues(rows);





/**
 * Removes duplicate rows from the current sheet.
 */

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetname');
  var data = sheet.getDataRange().getValues();

  data.reverse(); //reverses the row order.

  var last=sheet.getLastRow();
  var newData = new Array();
  for(i in data){
    //Logger.log(i);
    var row = data[i];
    //Logger.log(row[5]);
    var duplicate = false;
    for(j in newData){
      //Logger.log(newData[j][3]);
      if(row[3] == newData[j][3]){
        duplicate = true;
      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }

  newData.reverse(); // reverses your data back to its original order.

  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
  }
//  
  //

Use Textfinder class. See tag info page for official documentation. — TheMaster
– TheMaster, Commented May 20, 2020 at 12:40

Nabnub · Accepted Answer · 2020-05-20 11:55:54Z

2

If you want to remove always the same thing (i.e. "T" and "+0000"), you could use the following script:

The result obtained: 2020-02-23 12:14:06

CODE:

    // ---------- Menu ----------
// Add a Menu named Format Date to run the script

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Format Date')
  .addItem('Go!', 'FormatDate')
  .addToUi();
}


function FormatDate() {
var ss = SpreadsheetApp.getActiveSheet(),
    array = [];
ss.getRange("B2:B") // Choose the range here
    .getValues()
    .forEach(function (dates) {

      // "T" is replaced with a space: " " and "+0000" is replace with empty:""

      [["T", " "], ["+0000", ""]] 
            .map(function (a, i) {
                return dates = replace(dates.toString(), a[0], a[1])
            })
        array.push([dates])
    });

// You can set a different column to write the data
// Or keep B2:B to overwrite your data

  ss.getRange("B2:B") 
    .setValues(array)
}


function replace(d, a, r) {
return d.indexOf(a) > -1 ? d.split(a)
    .join(r) : d;
}

Credit: got inspired by JPV's code to a different question long time ago

edited May 20, 2020 at 11:55

answered May 20, 2020 at 11:32

Nabnub

1,0707 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ted Over a year ago

Hey, thanks for your answer. It works as function but when i try to incorporate it to my original script i get "replace is not defined". Is there anyway you can tell me how to incorporate it? I will attach the rest of my script on my original post. @Nabnub

Nabnub Over a year ago

You could proceed by calling the functions one after one, just call this one when it's needed. If the function should run on a specific sheet make the necessary changes by using getSheetByName.

Ted Over a year ago

The function is needed every time the first function is run but i cant seem to find a way to run it after the first one apart from incorporating it into it. But i get the error i mentioned.

Nabnub Over a year ago

Hi, if the solution did answer your initial question, please consider accepting as a Correct Answer by ticking it. Give it a try by searching a little bit and trying few lines, otherwise you won't learn much.

Collectives™ on Stack Overflow

Replace character using google apps script

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related