Looking for a faster function to add notes on specific cells-CodePudding

First, here is the link from my Google Sheet:

https://docs.google.com/spreadsheets/d/1vROojeTBYnSQy0IeYY0vkzump-CPoDd5fyFuLwRZsjQ/edit?usp=sharing

I have 2 sheets: one (Sheet1) which contains a list of machines with an IP address, a Hostname and a switch port. The objective of my script is to read the switch and the port of sheet 1, then put a note in the corresponding cell in sheet 2 (sheet 2 contains the diagram of the switches available with their 48 ports), the note must contain hostname and IP address.

The script works but adds the notes one by one, and it might take more than 30 minutes if my number of machines increases.

Do you have an idea to speed up the process?

CodePudding user response：

Issue:

You're currently iterating through the source values in Sheet1 and using TextFinder to find the corresponding cells in Sheet2. Using TextFinder iteratively greatly increases the amount of requests to the spreadsheet, and that slows down the script a lot (see Minimize calls to other services).

Along the same lines, you are setting the notes for each cell individually via setNote, instead of setting all of them at once with setNotes(notes), as Sergey said.

Solution:

I'd like to propose an alternative approach to minimize calls to the spreadsheet and so make the script considerably faster:

Retrieve the useful data from Sheet1 (note, switch and port), converting it to an array of objects for easier later retrieval.
Retrieve all the data in Sheet2 using getDataRange() and getValues().
Using map, iterate through the data in Sheet2, and for each row, check if either column A of the current row (for odd port numbers) or from two rows above (for even port numbers) contain a valid switch name (you can use find for that).
If no valid switch name is found, return an array of null for this map iteration (since providing null as a parameter to setNote removes the note).
If a valid switch name is found, iterate through that row's ports using map, and for each port, look for the valid combination of switch and port in the source data.
If a valid combination of switch and port is found, return the corresponding note for that map iteration.
If a valid combination is not found (that is, Sheet1 doesn't contain data for that combination), return null for that iteration.
Once you have build the 2D array of notes using the corresponding nested maps, set those notes to the port columns from Sheet2, using setNotes.

Code sample:

function actualiserNP2(){
  const SWITCH_COL = 1;
  const PORT_FIRST_COL = 4;
  const PORT_LAST_COL = 27; 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const Sheet2 = ss.getSheetByName('Sheet2'); 
  const Sheet1 = ss.getSheetByName('Sheet1');
  const tabGeneral = Sheet1.getRange(10,2, Sheet1.getLastRow(), Sheet1.getLastColumn()).getValues();
  const sourceData = tabGeneral.filter(row => row[7].includes("#")).map(row => {
    const [sw1a, sw1b] = row[7].split("#");
    const data = {
      "note": `${row[0]}\n\n${row[5]}`,
      "switch": sw1a,
      "port": sw1b
    }
    return data;
  });
  const targetData = Sheet2.getDataRange().getValues();
  const notes = targetData.map((row,i) => {
    const sourceSwitch = sourceData.find(sourceRow => sourceRow["switch"] === row[SWITCH_COL-1] || (i > 1 && sourceRow["switch"] === targetData[i-2][SWITCH_COL-1]));
    if (sourceSwitch) {
      const currentSwitch = sourceSwitch["switch"];
      const ports = row.slice(PORT_FIRST_COL-1, PORT_LAST_COL);
      const rowNotes = ports.map(port => {
        const sourceRow = sourceData.find(row => row["switch"] === currentSwitch && Number(row["port"]) === Number(port));
        if (sourceRow) return sourceRow["note"];
        else return null;
      });
      return rowNotes;
    } else {
      return new Array(PORT_LAST_COL-PORT_FIRST_COL 1).fill(null);
    }
  });
  Sheet2.getRange(1, PORT_FIRST_COL, Sheet2.getLastRow(), PORT_LAST_COL-PORT_FIRST_COL 1).setNotes(notes);
}

CodePudding user response：

I modified lamblichus's script to adapt it to the 3 ports. It works with this new script :

function actualiserNP1()
{
  const SWITCH_COL = 1;
  const PORT_FIRST_COL = 4;
  const PORT_LAST_COL = 27; 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const Sheet2 = ss.getSheetByName('Sheet2'); 
  const Sheet1 = ss.getSheetByName('Sheet1');
  const tabGeneral = Sheet1.getRange(10,2, Sheet1.getLastRow(), Sheet1.getLastColumn()).getValues();
  const data = [];
  const sourceData = tabGeneral.filter(row => row[7].includes("#") || row[11].includes("#") || row[15].includes("#")).map(row => { // On filtre les lignes ayant au moins un port
    
    //§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§ 001
    
    if(row[7]=='' && row[11]=='' && row[15]!='')
    {
      if(row[13]!=''){
        const [sw3a, sw3b] = row[15].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[13]}`,
          "switch": sw3a,
          "port": sw3b
        })
      }else{
        const [sw3a, sw3b] = row[15].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw3a,
          "port": sw3b
        })
      }
    }
    //§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§ 010

    if(row[7]=='' && row[11]!='' && row[15]=='')
    {
      if(row[9]!=''){
        const [sw2a, sw2b] = row[11].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[9]}`,
          "switch": sw2a,
          "port": sw2b
        })
        
      }else{
        const [sw2a, sw2b] = row[11].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw2a,
          "port": sw2b
        })
        
      }
    }
    //§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§ 011

    if(row[7]=='' && row[11]!='' && row[15]!='')
    {
      if(row[9]!=''){
        const [sw2a, sw2b] = row[11].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[9]}`,
          "switch": sw2a,
          "port": sw2b
        })
        
      }else{
        const [sw2a, sw2b] = row[11].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw2a,
          "port": sw2b
        })
      }
      // $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
      if(row[13]!=''){
        const [sw3a, sw3b] = row[15].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[13]}`,
          "switch": sw3a,
          "port": sw3b
        })
        
      }else{
        const [sw3a, sw3b] = row[15].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw3a,
          "port": sw3b
        })
        
      }
    }
    //§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§ 100

    if(row[7]!='' && row[11]=='' && row[15]=='')
    {
      const [sw1a, sw1b] = row[7].split("#");
      data.push({
        "note": `${row[0]}\n\n${row[5]}`,
        "switch": sw1a,
        "port": sw1b
      })
      
    }
  //§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§ 101

  if(row[7]!='' && row[11]=='' && row[15]!='')
    {
      if(row[13]!=''){
        const [sw3a, sw3b] = row[15].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[13]}`,
          "switch": sw3a,
          "port": sw3b
        })
        
      }else{
        const [sw3a, sw3b] = row[15].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw3a,
          "port": sw3b
        })
        
      }
      //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

      const [sw1a, sw1b] = row[7].split("#");
    
      data.push({
        "note": `${row[0]}\n\n${row[5]}`,
        "switch": sw1a,
        "port": sw1b
      })
      
    }
    //§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§ 110

    if(row[7]!='' && row[11]!='' && row[15]=='')
    {
      if(row[9]!=''){
        const [sw2a, sw2b] = row[11].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[9]}`,
          "switch": sw2a,
          "port": sw2b
        })
      }else{
        const [sw2a, sw2b] = row[11].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw2a,
          "port": sw2b
        })
       
      }
      
      //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

      
        const [sw1a, sw1b] = row[7].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw1a,
          "port": sw1b
        })
        
    }
    //§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§ 111

    if(row[7]!='' && row[11]!='' && row[15]!='')
    {
      if(row[9]!=''){
        const [sw2a, sw2b] = row[11].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[9]}`,
          "switch": sw2a,
          "port": sw2b
        })
        
      }else{
        const [sw2a, sw2b] = row[11].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw2a,
          "port": sw2b
        })
        
      }
      //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

      if(row[13]!=''){
        const [sw3a, sw3b] = row[15].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[13]}`,
          "switch": sw3a,
          "port": sw3b
        })
        
      }else{
        const [sw3a, sw3b] = row[15].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw3a,
          "port": sw3b
        })
        
      }
      //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

      
        const [sw1a, sw1b] = row[7].split("#");
      
        data.push({
          "note": `${row[0]}\n\n${row[5]}`,
          "switch": sw1a,
          "port": sw1b
        })
        
    }

    return data;
        
  });
  console.log(data)
  //console.log(sourceData);

  const targetData = Sheet2.getDataRange().getValues();
  const notes = targetData.map((row,i) => {
    const sourceSwitch = data.find(sourceRow => sourceRow["switch"] === row[SWITCH_COL-1] || (i > 1 && sourceRow["switch"] === targetData[i-2][SWITCH_COL-1]));
    if (sourceSwitch) 
    {
      const currentSwitch = sourceSwitch["switch"];
      const ports = row.slice(PORT_FIRST_COL-1, PORT_LAST_COL);
      const rowNotes = ports.map(port => {
      const sourceRow = data.find(row => row["switch"] === currentSwitch && Number(row["port"]) === Number(port));
      if (sourceRow) return sourceRow["note"];
      else return null;
      });
      return rowNotes;
    } else {
      return new Array(PORT_LAST_COL-PORT_FIRST_COL 1).fill(null);
    }
  });

  Sheet2.getRange(1, PORT_FIRST_COL, Sheet2.getLastRow(), PORT_LAST_COL-PORT_FIRST_COL 1).setNotes(notes);
}

I am a begginer so i did instructions for each possible case.