At this time Google Sheets doesn't include a way to customize the automatic data type assignation but it's possible to use Google Apps Script to automatically convert input from one type to another but it has some limitations but let start with the simplest solution, then on another Q&A we could talk about how to circumvent the known limitations and the problems that arise for cases of general interest.
It's possible that an add-on already exists. If you need a recommendation post a question on Software Recommendations
General steps
- Create an Apps Script project
- Add a simple on edit function
- Add the JavaScript to convert input in the form of
#unit to Google durations
- Complete the simple on edit function to return the result to the spreadsheet
Create an Apps Script project
- Click on Tools > Script editor
- Click on Untitled project or save the project to assign a name to the project
NOTE: If this is your first project and you don't have plans yet to create many, the name could be anything, but a short descriptive name always is better
Add an on edit simple trigger.
- Replace the default code by
function onEdit(e){
}
Add the JavaScript code
Complete the simple on edit function to return the result to the spreadsheet
For convenience, I added the code of the last two steps in a single code block.
function onEdit(e) {
var duration = stringToDuration(e.value);
if(e.value !== duration) {
e.range.setValue(duration);
e.range.setNumberFormat('[h]:mm'); // Here you can set the number format to use
}
}
/**
* Converts a string to a Google Sheet duration value
*
* @param {String} input The string to be converted
* @returns An Integer if the input format is supported otherwise returns the input
*/
function stringToDuration(input){
if(!typeof input === 'string') return input;
var date = new Date(0);
var match = input.match(/^(\d.*)(.)/);
var value = parseFloat(match[1]);
var isDuration = true;
switch(input[2]){
case 'm':
date.setMinutes(value);
break;
case 'h':
date.setHours(value);
break;
default:
output = input;
isDuration = false;
}
var output;
if(isDuration) {
var oneDayInMS = 24*60*60*1000;
output = date.getTime() / oneDayInMS;
} else {
output = input;
}
return output
}
REMARKS
- The above code just consider two cases,
#m (like 90m) and #h (like 1.5h). For a complete solution adapt the code of a JavaScript library like moment.js.
- The Google Apps Script triggers works on "best effort" base, this means that triggers like on edit, on form submit, etc. sometimes aren't triggered. I.E. if your type fast many values, some of the edit will trigger the onEdit function but others not, so you could have a time-driven trigger to scan the values that were not converted and then convert them.
- onEdit functions on bounded project scripts are available only for the bounded spreadsheet. To make it available to all your spreadsheets create an add-on, install it on your account and enable it on the spreadsheet that you want to use it.
