1

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

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);
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
rachel
  • 25
  • 6

1 Answers1

1

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();
Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • I tried pretty much that exact thing. I used `ss.getRange("G4:G11").setNumberFormat("yyyy-MM-dd");` after line 41 in my code but it didn't change a thing. Am I missing something? – rachel Feb 12 '22 at 21:44
  • I've updated my answer. – Yuri Khristich Feb 12 '22 at 21:46
  • that seems to work! It even works when I comment out the line to `setNumberFormat()`. I'm not familiar with `getDisplayValues();` vs `getValues();` so I'll have to read up on why that worked, but thank you, thank you! – rachel Feb 12 '22 at 21:52
  • `getDisplayValues()` returns you a string. Not a Date object. Then you paste it back on the target sheet as is. Yes, probably you don't even need the format in this case after all. – Yuri Khristich Feb 12 '22 at 21:56
  • gotcha. Thanks again! – rachel Feb 12 '22 at 22:09