CODEWITHSUNDEEP
google-apps-script
Ted
Ted

Reputation: 77

Replace character using google apps script

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

Upvotes: 0

Views: 1607

Answers (1)

Nabnub
Nabnub

Reputation: 1055

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

Upvotes: 2

Related Questions