skip to Main Content

This script import new clients from the source files and update the existing clients with possible new data from the client sheet. The problem I’m getting is that the script always update the header "Ultimo cambio" (last change in spanish) even if there is no new data on the client source file. Everything else works fine, I posted the full script because there might be a conflict between the code. I have changed the comments to english for easier reading. All sheets have the same headers btw.

function importarDatosClientes() {
  const folderId = 'xxxxxxxxxxxxxxxxxxxxxxx';
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let clientesSheet = spreadsheet.getSheetByName('Clientes');


  if (!clientesSheet) {
    clientesSheet = spreadsheet.insertSheet('Clientes');
    clientesSheet.appendRow(['Cliente', 'Ultimo cambio', /* Add other headers as necessary */]);
  }


  const headers = clientesSheet.getRange(1, 1, 1, clientesSheet.getLastColumn()).getValues()[0];
  const clientIndex = headers.indexOf('Cliente');
  const lastChangeIndex = headers.indexOf('Ultimo cambio');


  if (clientIndex === -1 || lastChangeIndex === -1) {
    throw new Error("Headers 'Cliente' and/or 'Ultimo cambio' not found in 'Clientes' sheet");
  }


  // Get existing clients
  let existingClients = {};
  if (clientesSheet.getLastRow() > 1) {
    const clientNames = clientesSheet.getRange(2, clientIndex + 1, clientesSheet.getLastRow() - 1).getValues();
    const clientData = clientesSheet.getRange(2, 1, clientesSheet.getLastRow() - 1, clientesSheet.getLastColumn()).getValues();
    clientNames.forEach((name, i) => {
      existingClients[name[0]] = clientData[i];
    });
  }


  while (files.hasNext()) {
    const file = files.next();
    const fileName = file.getName();


    if (fileName === 'Cliente tipo') {
      continue; // Ignore "Cliente tipo" file
    }


    const clienteSpreadsheet = SpreadsheetApp.openById(file.getId());
    const clienteSheet = clienteSpreadsheet.getSheets()[0];
    const clienteHeaders = clienteSheet.getRange(1, 1, 1, clienteSheet.getLastColumn()).getValues()[0];
    const clienteData = clienteSheet.getRange(2, 1, 1, clienteSheet.getLastColumn()).getValues()[0];


    let rowData = new Array(headers.length).fill('');
    let dataUpdated = false;
    headers.forEach((header, index) => {
      if (header !== 'Ultimo cambio') { // Don't import 'Ultimo cambio'
        const columnIndex = clienteHeaders.indexOf(header);
        const value = columnIndex >= 0 ? clienteData[columnIndex] : '';
        rowData[index] = value;


        // Check if there is any change in the data
        if (existingClients[fileName] && existingClients[fileName][index] !== value) {
          dataUpdated = true;
        }
      }
    });


    rowData[clientIndex] = fileName; // Ensure the client name is in the correct position


    if (existingClients[fileName]) {
      const existingRow = Object.keys(existingClients).indexOf(fileName) + 2;
      if (dataUpdated) {
        // Update 'Ultimo cambio' field if data has changed(this triggers even if no data has changed)
        rowData[lastChangeIndex] = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM/yyyy HH:mm:ss');
        clientesSheet.getRange(existingRow, 1, 1, rowData.length).setValues([rowData]);
      }
    } else {
      // For a new client, set the current date and time in 'Ultimo cambio'
      rowData[lastChangeIndex] = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM/yyyy HH:mm:ss');
      clientesSheet.appendRow(rowData);
      existingClients[fileName] = rowData; // Add the new client to the list of existing clients
    }
  }
}

I’ve tried to check data, store it and compare, nothing works for me. It always change the date when i run it.

2

Answers


  1. You need to re-order the last if-else as below:

    if (existingClients[fileName]) {
      const existingRow = Object.keys(existingClients).indexOf(fileName) + 2;
      if (dataUpdated) {
        // Update 'Ultimo cambio' field if data has changed(this triggers even if no data has changed)
        rowData[lastChangeIndex] = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM/yyyy HH:mm:ss');
        clientesSheet.getRange(existingRow, 1, 1, rowData.length).setValues([rowData]);
      } else {
        // For a new client, set the current date and time in 'Ultimo cambio'
        rowData[lastChangeIndex] = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM/yyyy HH:mm:ss');
        clientesSheet.appendRow(rowData);
        existingClients[fileName] = rowData; // Add the new client to the list of existing clients
      }
    }
    
    Login or Signup to reply.
  2. try:

    function importarDatosClientes() {
      const folderId = 'xxxxxxxxxxxxxxxxxxxxxxx';
      const folder = DriveApp.getFolderById(folderId);
      const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
      const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      let clientesSheet = spreadsheet.getSheetByName('Clientes');
    
      if (!clientesSheet) {
        clientesSheet = spreadsheet.insertSheet('Clientes');
        clientesSheet.appendRow(['Cliente', 'Ultimo cambio', /* Add other headers as necessary */]);
      }
    
      const headers = clientesSheet.getRange(1, 1, 1, clientesSheet.getLastColumn()).getValues()[0];
      const clientIndex = headers.indexOf('Cliente');
      const lastChangeIndex = headers.indexOf('Ultimo cambio');
    
      if (clientIndex === -1 || lastChangeIndex === -1) {
        throw new Error("Headers 'Cliente' and/or 'Ultimo cambio' not found in 'Clientes' sheet");
      }
    
      // Get existing clients
      let existingClients = {};
      if (clientesSheet.getLastRow() > 1) {
        const clientNames = clientesSheet.getRange(2, clientIndex + 1, clientesSheet.getLastRow() - 1).getValues();
        const clientData = clientesSheet.getRange(2, 1, clientesSheet.getLastRow() - 1, clientesSheet.getLastColumn()).getValues();
        clientNames.forEach((name, i) => {
          existingClients[name[0]] = clientData[i];
        });
      }
    
      while (files.hasNext()) {
        const file = files.next();
        const fileName = file.getName();
    
        if (fileName === 'Cliente tipo') {
          continue; // Ignore "Cliente tipo" file
        }
    
        const clienteSpreadsheet = SpreadsheetApp.openById(file.getId());
        const clienteSheet = clienteSpreadsheet.getSheets()[0];
        const clienteHeaders = clienteSheet.getRange(1, 1, 1, clienteSheet.getLastColumn()).getValues()[0];
        const clienteData = clienteSheet.getRange(2, 1, 1, clienteSheet.getLastColumn()).getValues()[0];
    
        let rowData = new Array(headers.length).fill('');
        let dataUpdated = false;
        headers.forEach((header, index) => {
          if (header !== 'Ultimo cambio') { // Don't import 'Ultimo cambio'
            const columnIndex = clienteHeaders.indexOf(header);
            const value = columnIndex >= 0 ? clienteData[columnIndex] : '';
            rowData[index] = value;
    
            // Check if there is any change in the data
            if (existingClients[fileName] && existingClients[fileName][index] !== value) {
              dataUpdated = true;
            }
          }
        });
    
        rowData[clientIndex] = fileName; // Ensure the client name is in the correct position
    
        if (existingClients[fileName]) {
          const existingRow = Object.keys(existingClients).indexOf(fileName) + 2;
          if (dataUpdated) {
            // Update 'Ultimo cambio' field if data has changed
            rowData[lastChangeIndex] = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM/yyyy HH:mm:ss');
            clientesSheet.getRange(existingRow, 1, 1, rowData.length).setValues([rowData]);
          }
        } else {
          // For a new client, set the current date and time in 'Ultimo cambio'
          rowData[lastChangeIndex] = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM/yyyy HH:mm:ss');
          clientesSheet.appendRow(rowData);
          existingClients[fileName] = rowData; // Add the new client to the list of existing clients
        }
      }
    }
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search