Search code examples
google-apps-scriptgoogle-sheetsappend

Appending Row with Script Changes Text-Formatted Value to Time-Formatted Value

Using the script below, numbers formatted as text are being changed to time format, even though I have Column E2:E in Logged_Data sheet formatted as "Plain text". What is the reason for this? And what can I do to fix it?

In Data_Input!D8, the cell is formatted as "Plain text":

Screenshot1

Screenshot2

However, when it appears on the Logged_Data sheet, it is formatted as time:

Screenshot3

Here is the script I am using:

function transposeAndAppend() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let shtIn = ss.getSheetByName("Data_Input");
  let shtOut = ss.getSheetByName("Logged_Data");

  let responses = shtIn.getRange("D4:D11").getValues();
  //responses is a 2d array that looks like this [[a],[b],[c]]
  
  //first transpose it
  let transResp = responses[0].map((resp,i)=>responses.map(r => r[i]))
  //we now have [[a,b,c]] 

  shtOut.appendRow(transResp.flat())

    //Clear the 'form'
  shtIn.getRange("D4:D11").clearContent();
}

According to Hyde at Google Support, https://support.google.com/docs/thread/99094988/can-you-format-the-data-when-doing-appendrow?hl=en, this should not be happening.

Solution

  • I believe your goal is as follows.

    • You want to append the values from "D4:D11" of "Data_Input" sheet to columns "A" to "H" of "Logged_Data" sheet.
    • The number format of "D4:D11" of "Data_Input" is the plain text.

    I would like to propose using the copyTo method of Class Range. The modified script is as follows.

    Modified script.

    function transposeAndAppend() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let shtIn = ss.getSheetByName("Data_Input");
  let shtOut = ss.getSheetByName("Logged_Data");

  // I modified the below script.
  const srcRange = shtIn.getRange("D4:D11");
  const dstRange = shtOut.getRange(shtOut.getLastRow() + 1, 1);
  srcRange.copyTo(dstRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);
}

    Reference: