CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsduplicates
NSK
NSK

Reputation: 1

Adding number at the end of duplicate text string to make unique document number - Apps script

I am new to scripts and have gotten stuck on a recent project.

Problem:

I am trying to write a script that will generate unique document numbers based on a number of parameters in the columns of a google sheet. Since there are multiple documents which share the same room and floor information my current script creates duplicates - what I would like to achieve is that the script searches the duplicate value and adds a number at the end to make it unique.

Data:

This is a sample of what the spreadsheet looks like

So ideally what I would like is instead of multiple rows which say: "PROJECT1-1-01-T-" I would have individuals numbers labelled as: "PROJECT1-1-01-T-00" "PROJECT1-1-01-T-01" etc.

Current Code:

function onOpen () {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('Create Document Number');
menu.addItem('Create Base Document Number','createbaseDocumentNumber');
menu.addToUi();

}



function createbaseDocumentNumber() {

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet =   ss.getActiveSheet();
const rows = sheet.getDataRange().getDisplayValues();

rows.forEach(function(row,index){

if (index === 0) return;
if (!row[2]) return;


if(row[4] == "TECHNICAL"){
var docNum = `PROJECT1-${row[2]}-${row[3]}-`


sheet.getRange(index + 1,1).setValue(drawNum + "T"); } 

if(row[4] !== "TECHNICAL"){
var docNum = `PROJECT1-${row[2]}-${row[3]}-`


sheet.getRange(index + 1,1).setValue(docNum + "E"); } 

  
})


}

If anyone has any ideas I would really appreciate the help!

Upvotes: 0

Views: 164

Answers (1)

JPV
JPV

Reputation: 27282

For a non-scripted solution you can try in A1 (after clearing contents in column A)

={"Document number"; Arrayformula (
  if (len(E2:E), 
  "PROJECT1-"&C2:C&"-"&D2:D&"-"&LEFT(E2:E)&"-"
  &text(countifs(C2:C&D2:D&E2:E, C2:C&D2:D&E2:E, row(E2:E), " 
  <="&row(E2:E))-1, "00")
 ,))}

enter image description here

Upvotes: 1

Related Questions