CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
sianh276
sianh276

Reputation: 5

Looping in Google Sheets

Thank you for coming by my question.

I create a macro for splitting the timestamp in Google Sheets like this.

/** @OnlyCurrentDoc */

function SplittingTimestamp() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B2').activate();
  spreadsheet.getCurrentCell().setFormula('=SPLIT(A2," ")');
  spreadsheet.getRange('B3').activate();
};

Basically, the macro is to split the timestamp column whenever a new form submit is sent. And the Timestamp is the A column and A will be split into B (Date) and C(Time). I want to loop this macro for each new submission.

Illustration

I understand that this line of code spreadsheet.getCurrentCell().setFormula('=SPLIT(A2," ")'); should be A3, A4, A5 and so on whenever a new submission is received. But I don't know how to loop this function. I came across some other problems but I cannot figured it out.

Upvotes: 0

Views: 100

Answers (1)

Jason E.
Jason E.

Reputation: 1221

If you are willing to make it as a single formula that will SPLIT all the date and time in column A, you can try this formula below:

=ARRAYFORMULA(SPLIT(FILTER(A2:A, A2:A<>""), " "))

enter image description here

Let me know if this solves your problem.

Upvotes: 1

Related Questions