0

Google App Script Spreadsheets

I'm looking for a way to edit the contents of a cell. I have searched through Spreadsheet Service docs and haven't found much that would help me for what I was hoping to accomplish.

What I want to do is manipulate the contents of a cell. An example of this would be:

  • Cell A1 contains "1PM - 5PM"
  • I want to split A1 into two cells, say B1 to be "1PM" and C1 to be "5PM"
  • Then I want to change cells B1 and C1 to military time. So in the end B1 would be 13:00 and C1 would be 17:00)

Any help is appreciated! Thanks!

1 Answer 1

1

Every part of the example below can be done more succinctly, efficiently, elegantly, and adaptably -- it's really not good code -- but I wanted to make the components as clear as possible so you can see how it works.

First, here's an oversimplified function to convert hours to 24 hour format. I don't recommend that you use exactly this because it'll only work with on-the-hour times formatted exactly as you wrote them, e.g. "3PM" or "2AM" -- "2:30 pm" would not work at all. For more sophisticated time conversion, check out this answer: convert 12-hour hh:mm AM/PM to 24-hour hh:mm

function oversimplified_time_format_converter(input){
  if(input.indexOf("PM")!==-1){
    var hour = 12 + (input.substring(0,input.indexOf("PM"))*1)
    if(hour>24){hour-=24}
  } else {
    var hour = input.substring(0,input.indexOf("AM"))
    }
  return hour
  }

Next, here's a function to perform the task you mentioned, which uses the oversimplified time format converter function above.

function yourFunction() {

  //First, locate your data:
  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheet/yourspreadsheetURL").getSheetByName("YourSheetName")
  var cell = sheet.getRange("A1")
  var cell_contents = cell.getValue() //The cell contents will be "1PM -5PM"

  //Next, locate the cells where the results will go
  var first_cell_to_the_right = cell.offset(0,1) //This is cell B1
  var second_cell_to_the_right = cell.offset(0,2) //This is cell C1

  //Next, get the text, split it into separate strings
  var first_part = cell_contents.substring(0,cell_contents.indexOf(" - ")) //This will be "1PM"
  var second_part = cell_contents.substring(cell_contents.indexOf(" - ")+3) //This will be "5PM"

  //Now convert to 24-hour time format:
  first_part = oversimplified_time_format_converter(first_part)
  second_part = oversimplified_time_format_converter(second_part)

  //Now write the results to your spreadsheet
  first_cell_to_the_right.setValue(first_part) //Set value of B1 to "1PM"
  second_cell_to_the_right.setValue(second_part) //Set value of C1 to "5PM"
}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.