Google spreadsheet script: append row in plain text

Question

I am appending rows from one sheet to another, and the source sheet has date format like: Sat, Apr 29, 2017

When appended to the target sheet, it changes to 4/29/2017 format. Since I am appending, I can't set the cell formats before appending.

Is there anyway I can set the whole sheet to be always in plain text? or somehow force the append to keep the formatting like the source and not to interpret values?

The command I am using for append is:

targetSheet.appendRow(row);

Exterminatory

Why can't you set the cell formats? range.setNumberFormat("@"); should set it to "plain text". – Intromission

@SpiderPig because the row that is going to be appended is not in the range yet. – Exterminatory

What you probably need is Range.copyFormatToRange to copy the formating from the source sheet. – Intromission

Answer 1

While it might not be a clean solution, something for which I am myself looking as well, this might be a workaround you could use.

Prepend an apostrophe or another sign that would not be recognized as part of the date. This will force the spreadsheet to interpret it as text.

Maguire

Answer 2

There does not seem to be a way to use sheet.appendRow(data).set to append a row and use .setNumberFormat. No matter how I try it, the .setNumberFormat is not an option that pops up.

Here's what works for my situation, which might be helpful to others: I am trying to append a row in which one of the cells has a serial number that starts with "000". The zeros are automatically removed if I don't put some text in the cell with it, unfortunately. Adding the text will be more work later. Here is the solution that works for me:

while (files.hasNext()) {
    var file = files.next();
    var name = file.getName(); // The name of the file is a serial number that starts with "000"
    var url = file.getUrl();
    var lastUpdate = file.getLastUpdated();
    var description = file.getDescription();

    var lastRow = sheet.getLastRow();
    sheet.getRange(lastRow + 1, 1).setValue(name).setNumberFormat("@");
    sheet.getRange(lastRow + 1, 2).setValue(url);
    sheet.getRange(lastRow + 1, 3).setValue(lastUpdate);
    sheet.getRange(lastRow + 1, 4).setValue(description);
}

It is not as simple as using sheet.appendRow(data).set, but it does append a row and sets the format to plain text. Hopefully, someone knows an easier way.

Here's more information from Google:

1. setNumberFormats(numberFormats)
2. About date and time values

Cothurnus

Answer 3

One option is to append the row and then clear the formats.

function appendInPlainText() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(["dummy", "data"]);
  sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).clearFormat();
  SpreadsheetApp.flush();
}

Noisette

That only clears text formatting, not number formatting. Even if it would clear number formatting it wouldn't restore the original formatting. – Maguire

Answer 4

data can be [[1,2,3],[4,5,6]] In this way you can append new data into a sheet without getting the last row.

valueInputOption: "RAW" will not parse the data format. USER_ENTERED will parse data format.

Sheets.Spreadsheets.Values.append({
        majorDimension: "ROWS",
        values: data,
    },
    SheetID,
    SheetName,
    //valueInputOption: "RAW" OR "USER_ENTERED"
    //Reference here:https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
    { valueInputOption: "RAW" }
)

Bremsstrahlung

Answer 5

sheet.appendRow(["'"+date]);

Oxford