Goal

Write a script that does the following:

• get data from sheet
• modify column to flip names from [Last, First] to [First Last]
• modify 2 columns to abbreviate company names & statuses
• write resulting data to another spreadsheet without changing the format
• add timestamp for when data copied

Problem

I can get the data BUT it writes everything back as plain text. Thus instead of dates writing out as "yyyy-MM-dd", they write out as something like this:
Mon Oct 19 2020 01:00:00 GMT-0400 (Eastern Daylight Time)

Expectation: screenshot of dates as "yyyy-MM-dd"

Result: screenshot of dates as whatever this garble is

I have googled extensively and can't seem to find a solution. I believe my problem is with using toString() in the Array.map. I'm not sure how to restrict the map method to only the columns that need modifying. Right now it affects the whole array.
(I used the code from Google Apps Script for Multiple Find and Replace in Google Sheets to write this part)

//-----FIND AND REPLACE FOR COMPANY & STATUS ABBREVIATIONS
function replaceInSheet(initArray, to_replace, replace_with){
  //Loop over rows in array
  for(var row in initArray ){
    //Use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = initArray[row].map(function(originalValue){
        return originalValue.toString().replace(to_replace,replace_with);
      });

    //Replace the original row values with the replaced values
    initArray[row] = replaced_values;
  }
}

Question--> How do I get the output of my script to format dates in two of my columns, correctly?

Attempted Solutions that didn't work

• I tried swapping the name flip code to happen after the abbreviation code so I could add a setNumberFormat('yyyy-MM-dd') within the name flip for loop. I couldn't figure out how to apply this to columns within my array. Something like initarray[x][5].setNumberFormat("yyyy-MM-dd") gave me an error saying "TypeError: initArrayx.setNumberFormat is not a function"
• I tried adding code before (and then even after) .setValues() at the end to change the format. Some resources I referenced:
  • setNumberFormat('yyyy-MM-dd') from stackoverflow: Set cell format with google apps script
  • Utilities.formatDate(new Date(), "CST", "yyyy-MM-dd") from stackoverflow: Get today date in google appScript
  • website post by BLACKCJ: "Cell Number Formatting with Google Apps Script"
  • google's developer documentation
  • and all sorts of other articles, blogs, forums, etc.
• I tried writing a completely separate function to change the format after my script runs. Nope. I can't get those 2 columns to format as dates

Code & Sample Spreadsheet

Here's the whole code I'm using, modified to work with a sample google sheet I created just for the purposes of this question.

Sample Google Sheet:
https://docs.google.com/spreadsheets/d/1Ys77hQHHajIo-Xaxyom0SVnyVMZ6bKOT8Smpadd2jv4/edit?usp=sharing

Script:

// ==================================================
// FUNCTION TO RUN
// ==================================================
function syncData(){
  //Ger Source Data
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var thisSheet = ss.getSheetByName("source");
  var thisData = thisSheet.getRange("A4:M11");
  var initArray = thisData.getValues();

  //Get Target Location
  var toSheet = ss.getSheetByName("target");
  var toRange = toSheet.getRange("A4:M11"); //Range starts at A4

  //CHANGE [LAST, FIRST] TO [FIRST LAST]
  for (var x = 0; x < initArray.length; x  ){
    var indexOfFirstComma = initArray[x][0].indexOf(", ");
    if(indexOfFirstComma >= 0){
      //If comma found, split and update values in the values array
      var lastAndFirst = initArray[x][0];
      //Update name value in array
      initArray[x][0] = lastAndFirst.slice(indexOfFirstComma   2).trim()   " "   lastAndFirst.slice(0, indexOfFirstComma).trim();
    }
  }

  //ABBREVIATE COMPANY
  replaceInSheet(initArray, 'Bluffington School','BLF HS');
  replaceInSheet(initArray, 'Honker Burger','HBGR');
  replaceInSheet(initArray, 'Funky Town','FT');

  //ABBRIVIATE STATUS
  replaceInSheet(initArray, 'Regular','Staff');
  replaceInSheet(initArray, 'Contractual','Part');
  replaceInSheet(initArray, 'Temporary','Temp');
    
  //Clear Target Location
  var toClear = toSheet.getRange("A4:M11")
  toClear.clearContent();

  //Write updated array to target location
  toRange.setValues(initArray);

  //Write timestamp of when code was last run
  setTimeStamp(toSheet);
}


//-----FIND AND REPLACE FOR COMPANY & STATUS ABBREVIATIONS
function replaceInSheet(initArray, to_replace, replace_with){
  //Loop over rows in array
  for(var row in initArray ){
    //Use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = initArray[row].map(function(originalValue){
        return originalValue.toString().replace(to_replace,replace_with);
      });

    //Replace the original row values with the replaced values
    initArray[row] = replaced_values;
  }
}


//-----ADD TIMESTAMP FOR WHEN THE SCRIPT LAST RAN
function setTimeStamp(toSheet) {
  var timestamp = Utilities.formatDate(new Date(), "CST", "yyyy-MM-dd @ h:mm a");
  toSheet.getRange('F1').setValue(timestamp);
}

CodePudding user response：

setNumberFormat('yyyy-MM-dd') is a good solution but it's a method of a Range of the sheet. Not an array.

To apply the format you need to get a range first. Something like this:

toSheet.getRange('G4:G').setNumberFormat('yyyy-MM-dd');

And there is one more thing ) Try to change this line:

var initArray = thisData.getValues();

to:

var initArray = thisData.getDisplayValues();