Category: google-sheets

Hi anyone knows how I can simplify this code?
This is working, however it takes time to update the datetime column

function onEdit(e) {
var row = e.range.getRow();
var ws = “Consults Form Responses”;
if(row > 1 && e.source.getActiveSheet().getName() === ws)
{addDateAnswered(e);}
AllFormat();
}

function addDateAnswered(e){
var row = e.range.getRow();
var validity = e.source.getActiveSheet().getRange(row,14).getValue();

if(validity == “VALID” || validity == “INVALID”)
{setDateAnswered(e);}
else if(validity == “DUPLICATE” || validity == “NFA”)
{e.source.getActiveSheet().getRange(row,17).setValue(validity)}
else
{e.source.getActiveSheet().getRange(row,17).setValue(“PENDING”);}

var AllRange = ws.getRange(“A2:P”);
AllRange.setVerticalAlignment(“middle”);

var AllCenter = ws.getRange(“A2:A” && “F2:F” && “J2:K” && “M2:M”);
AllCenter.setHorizontalAlignment(“center”);

var TestLeft = ws.getRange(“O”);
TestLeft.setHorizontalAlignment(“left”);
}

function setDateAnswered(e){

var row = e.range.getRow();
var col = e.range.getColumn();
var TimeStamp = Utilities.formatDate(new Date(), ‘GMT+8:00’, ‘MM/dd/yyyy hh:mm:ss a’);

if(col === 14 && row > 1)
{e.source.getActiveSheet().getRange(row,17).setValue(TimeStamp);}
}

I would like to be able to click on something which will open a new google sheet based on a template. I would need cell A1 to contain a number which increases by 1 every time the template is opened. Ideally I would like the title to match this new number.

I am working on developing a purchase order form and want to make sure that no matter who opens the template sheet, they will be given their own PO number in order of when they opened it to avoid multiple people using the same number.

I have limited scripting experience so i have been performing a lot of google searches and trying to map out how to ask this properly. I have tried a macro that makes the title the same as cell A1 but no luck. I still have no idea how to automatically generate an incremental number for the title based on the time it was clicked.

I have two tables, namely Table A and Table B. Each table I want to identify the name of the table in column C
matches the table names that have been defined in column A.

The script I’ve made is like this:

tableName () function {
  let ss = SpreadsheetApp.getActiveSpreadsheet ();
  let cColumn1 = ss.getRange ('C3: C7');
  let cColumn2 = ss.getRange ('C11: C15');

  let tableName1 = ss.getRange ('C2'). getValue ();
  let tableName2 = ss.getRange ('C9'). getValue ();

  cColumn1.setValues   (tableName1);
  cColumn2.setValues   (tableName2);
}

But my script is still manual, we have to specify the start line and end line of the table.
To identify the name of the table I’ve ever made with a formula, so with this formula, we don’t need to specify the position
the name of the reference table.

= ARRAYFORMULA (IF ((B: B = "") + (B: B = "Name") ,, VLOOKUP (ROW (A: A), IF (LEN (REGEXEXTRACT (A: A, "TD+") ), {ROW (A: A), REGEXEXTRACT (A: A, "TD+")}, 0), 2)))

This formula is very heavy if the amount of data is large.

My Spreadsheet.