Disable autoformat in Google Spreadsheet-CodePudding

I want to fill a spreadsheet with information about classes (in an educational context) We have a class named 1AM and it's auto formatted to a datetime.

I have tried to set the column to plain text but it doesn't seem to change the auto formatting.

let ssClasses = SpreadsheetApp.create("classes");
let column = ssClasses.getRange("A1:E200");
column.setNumberFormat("@");

How to disable auto formatting via Google Apps script in a google Spreadsheet?

CodePudding user response：

I made some tests that can help you with this issue. From what I can see, the result of the format will depend on the type of trigger you are using, and if the code was run before the "1AM" was added or after.

For example, I tested 2 types of onEdit triggers (

With onOpen():

function onOpen(e) {
  let ssClasses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("classes3");
  let column = ssClasses.getRange("A1:E200");
  column.setNumberFormat('@');
}

CodePudding user response：

Try this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
  if (!e.value || !e.value.match(/^(1:00 AM)$/i)) return;
  e.range.setNumberFormat('@').setValue('1AM');
}