RE: Duplicating values every time script is running

[TimeCard Admin]

Hi All, 

I am new to app script and got stuck in here. please help!

1) I have two sheet (sheet 1 & sheet 2) where I copy data from one sheet-1 to sheet-2 and make changes after copying it to sheet-2. However, every time I add a new row in sheet-1, I run the script, but its copying the same data multiple times every time I run it.

however, I want run the script every minutes and only add new row or new value in sheet-2 not same data. 

below is my current code. 

function onOpen(e) {

  let ui = SpreadsheetApp.getUi();

  ui.createMenu('🤖 Automation Tools')

    .addItem('Move reps to individual sheets', 'moveRows')

    .addToUi();

};

function moveRows() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getActiveSheet();

  const engineeringSheet = SpreadsheetApp.openById('1bpiuP2go7gJkBxUlcw5ZO2qtaMt0b-PtpVG7NuYF6Mo').getSheetByName('Sheet1');

  const engineeringLastRow = engineeringSheet.getLastRow();

 

  const carpentrySheet = SpreadsheetApp.openById('1-cFgwCeF0OF4jq4qb4cavCd6ALOTP7jz1Qe37_3AObA').getSheetByName('Sheet1');

  const carpentryLastRow = carpentrySheet.getLastRow();

  const electricalSheet = SpreadsheetApp.openById('1b-Nk3UtSvARU75CVuD_ykQn_P38sxTHiXabDPqroqx0').getSheetByName('Sheet1');

  const electricalLastRow = electricalSheet.getLastRow();

  const chassisSheet = SpreadsheetApp.openById('14MpwbVt46GxIht5cMDK25ZvBAZRtUppqfYJT3DJbHsc').getSheetByName('Sheet1');

  const chassisLastRow = chassisSheet.getLastRow();

  const kitchenSheet = SpreadsheetApp.openById('11EqObcdzFI4L7F3kQOCjY5TzilWwh7x53TEyB-wCcE0').getSheetByName('Sheet1');

  const kitchenLastRow = kitchenSheet.getLastRow();

  const paintSheet = SpreadsheetApp.openById('19cZwOySmgY0ATRs3tP8anXu49jlhkeXTXm-ZAwRUzOY').getSheetByName('Sheet1');

  const paintLastRow = paintSheet.getLastRow();

  const panelSheet = SpreadsheetApp.openById('1BacQcTljlSWfWvI6gFAKi6SUq9fDaVXfC1H8ylUdXG8').getSheetByName('Sheet1');

  const panelLastRow = panelSheet.getLastRow();

  const plumbingSheet = SpreadsheetApp.openById('1NPRtAsyKkadS1f4lGvc1qL5OgFuPwgZPrBTBrJG6KF0').getSheetByName('Sheet1');

  const plumbingLastRow = plumbingSheet.getLastRow();

  const productionSheet = SpreadsheetApp.openById('1Dk30-gagUhFU9Kar3nksHczNDZ-37fiBB9sEAE83px0').getSheetByName('Sheet1');

  const productionLastRow = productionSheet.getLastRow();

  const storesSheet = SpreadsheetApp.openById('1pJzbPZPgbfveFPebN1-heQLJl8z2k4G088W4wE1V9xU').getSheetByName('Sheet1');

  const storesLastRow = storesSheet.getLastRow();

  const waterdivisionSheet = SpreadsheetApp.openById('1YiZ8yEpFMGT35EAhQfH-yRB2kYF48jrgcdLneoQPruA').getSheetByName('Sheet1');

  const waterdivisionLastRow = waterdivisionSheet.getLastRow();

  let lastRow = sheet.getLastRow();

  let sortRange = sheet.getSheetValues(2,1,lastRow, 9);

  Logger.log(lastRow)

  // Logger.log(typeof(sortRange))

  // Logger.log(sortRange.length);

  let engineeringCounter = 1;

  let carpentryCounter = 1;

  let electricalCounter = 1;

  let chassisCounter = 1;

  let kitchenCounter = 1;

  let paintCounter = 1;

  let panelCounter = 1;

  let plumbingCounter = 1;

  let productionCounter = 1;

  let storesCounter = 1;

  let waterdivisionCounter = 1;

  for (var i = 1; i <= sortRange.length; i++) {

    let Division = sheet.getRange(i,9).getValue();

    console.log(i + " - " + Division);

      if (Division == "Engineering") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        engineeringSheet.getRange(engineeringLastRow+engineeringCounter, 1, 1, 9).setValues(rowValues);

        engineeringCounter++;

      }

      if (Division == "Carpentry") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        carpentrySheet.getRange(carpentryLastRow+carpentryCounter, 1, 1, 9).setValues(rowValues);

        carpentryCounter++;

      }

      if (Division == "Electrical") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        electricalSheet.getRange(electricalLastRow+electricalCounter, 1, 1, 9).setValues(rowValues);

        electricalCounter++;

      }

      if (Division == "Chassis") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        chassisSheet.getRange(chassisLastRow+chassisCounter, 1, 1, 9).setValues(rowValues);

        chassisCounter++;

      }

      if (Division == "Kitchen") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        kitchenSheet.getRange(kitchenLastRow+kitchenCounter, 1, 1, 9).setValues(rowValues);

        kitchenCounter++;

      }

      if (Division == "Paint") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        paintSheet.getRange(paintLastRow+paintCounter, 1, 1, 9).setValues(rowValues);

        paintCounter++;

      }

      if (Division == "Panel") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        panelSheet.getRange(panelLastRow+panelCounter, 1, 1, 9).setValues(rowValues);

        panelCounter++;

      }

      if (Division == "Plumbing") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        plumbingSheet.getRange(plumbingLastRow+plumbingCounter, 1, 1, 9).setValues(rowValues);

        plumbingCounter++;

      }

      if (Division == "Production") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        productionSheet.getRange(productionLastRow+productionCounter, 1, 1, 9).setValues(rowValues);

        productionCounter++;

      }

      if (Division == "Stores") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        storesSheet.getRange(storesLastRow+storesCounter, 1, 1, 9).setValues(rowValues);

        storesCounter++;

      }

      if (Division == "Water Division") {

        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();

        waterdivisionSheet.getRange(waterdivisionLastRow+waterdivisionCounter, 1, 1, 9).setValues(rowValues);

        waterdivisionCounter++;

      }

  };

};

[Emerson Maia]

It looks like you're experiencing a common issue when working with Google Sheets scripts where data is copied multiple times, causing duplicates. This usually happens when the script is run multiple times without checking to see if the data has already been copied. , you can try the following to fix this.

function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('🤖 Automation Tools') .addItem('Move reps to individual sheets', 'moveRows') .addToUi(); } function moveRows() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getActiveSheet(); const lastRow = sheet.getLastRow(); const data = sheet.getRange(2, 1, lastRow - 1, 9).getValues(); const sheets = { 'Engineering': getSheet('1bpiuP2go7gJkBxUlcw5ZO2qtaMt0b-PtpVG7NuYF6Mo'), 'Carpentry': getSheet('1-cFgwCeF0OF4jq4qb4cavCd6ALOTP7jz1Qe37_3AObA'), // Adicione todas as outras divisões aqui }; data.forEach(row => { const division = row[8]; // A divisão está na nona coluna if (sheets[division]) { const lastRow = sheets[division].getLastRow(); sheets[division].getRange(lastRow + 1, 1, 1, 9).setValues([row]); } else { console.error(`Sheet for division ${division} not found`); } }); } function getSheet(spreadsheetId) { return SpreadsheetApp.openById(spreadsheetId).getSheetByName('Sheet1'); }

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4de9b446-98fb-4e64-bb74-1504693c8bf2n%40googlegroups.com.

[TimeCard Admin]

Hi Emerson,

Thank you very much for your support, however i am bit confused where will i add these code ? 

will i delete my existing code? 

see this below code is only for two department engineering and carpentry, and which code i will replace? 

function onOpen(e) {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('🤖 Automation Tools')
    .addItem('Move reps to individual sheets', 'moveRows')
    .addToUi();
};

function moveRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const engineeringSheet = SpreadsheetApp.openById('1bpiuP2go7gJkBxUlcw5ZO2qtaMt0b-PtpVG7NuYF6Mo').getSheetByName('Sheet1');
  const engineeringLastRow = engineeringSheet.getLastRow();
 
  const carpentrySheet = SpreadsheetApp.openById('1-cFgwCeF0OF4jq4qb4cavCd6ALOTP7jz1Qe37_3AObA').getSheetByName('Sheet1');
  const carpentryLastRow = carpentrySheet.getLastRow();

  let lastRow = sheet.getLastRow();

  let sortRange = sheet.getSheetValues(2,1,lastRow, 9);

  Logger.log(lastRow)
  // Logger.log(typeof(sortRange))
  // Logger.log(sortRange.length);

  let engineeringCounter = 1;
  let carpentryCounter = 1;

  for (var i = 1; i <= sortRange.length; i++) {
    let Division = sheet.getRange(i,9).getValue();
    console.log(i + " - " + Division);

      if (Division == "Engineering") {
        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();
        engineeringSheet.getRange(engineeringLastRow+engineeringCounter, 1, 1, 9).setValues(rowValues);
        engineeringCounter++;
      }

      if (Division == "Carpentry") {
        let rowValues = sheet.getRange(i, 1, 1, 9).getValues();
        carpentrySheet.getRange(carpentryLastRow+carpentryCounter, 1, 1, 9).setValues(rowValues);
        carpentryCounter++;
      }

  };
};

Your help is highly appreciated! 

[TimeCard Admin]

This is how my sheet looks like.

[Emerson Maia]

Good morning, I'm in position today at the end of the day, I look at your code.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/df9ec81c-d48a-4f28-bf69-465033a7c829n%40googlegroups.com.

[TimeCard Admin]

Hi Emerson, 

Good morning, I am waiting for your help. However there is a bit of update from my side. 

I have tried your code but it is not working it is copying the same data as before. Please help :D