Google App Script: Dynamically add new rows in Google Sheets-CodePudding

I'm working on a sheet to build a list of products to import into Shopify.

For this, I have a pdf of some basic data (that is irrelevant here) out of which I build a string to crawl the product supplier's website and format the data in a way suitable for import in Shopify.

The products have a varying number of images (1 - 8), so I'm trying to build my script in a way that if a product has more than one image, I am trying to add additional rows under it and add every image past the first into a new row.

Here is my code:

function iterateThroughRows() {

  // get spreadsheet
  const sheet = SpreadsheetApp.getActive().getSheetByName("MySheet");
  const data  = sheet.getDataRange().getValues();

  // Loop over rows
  data.forEach( (row, rowIndex) => {

    const imageSrcArray = [ /* list of image URLs, fetched from remote server */ ]

    imageSrcArray.forEach( (img, arrayIndex) => {
      if(arrayIndex == 0) { // for the first array item, just add it to the current row
        const imageCell = sheet.getRange(rowIndex   1, 24)
        imageCell.setValue( imageSrcArray[arrayIndex] )
      } else { // for each array item past the first one, add a new row and enter the value there
        sheet.insertRows(rowIndex)
        const imageCell = sheet.getRange(rowIndex   arrayIndex   1, 24)
        imageCell.setValue( imageSrcArray[arrayIndex] )
      }
    })

    // adding some more values to other cells

 });
}

As is this doesn't really work.

I worked on this all day yesterday and had a version using insertRowAfter() that did add additional rows, but added them all lumped together (i.e. there would be 15 rows after the first product, but none after any of the others). But since Google App Script doesn't have version control I lost that version.

I think the problem was that the forEach seems to move on to the newly created rows and keeps adding things from there rather than moving on to the initial next row.

So I'm more or less at the end of my wit with this. Any advise on how to properly do this would be highly appreciated.

CodePudding user response：

I can understand your frustration, it is indeed because you care calculating the row based on the sheet in its version before you added new rows to it.

So my proposal would be to do this, as the currentRow allows you to track the current row you are working on. I also updated the insertRowAfter(), as I assume this is what you actually wanted to do.

let currentRow = 1;

data.forEach( (row, rowIndex) => {

    const imageSrcArray = [ "img1URL", "img2URL"]
    if( !imageSrcArray.length ) return

    imageSrcArray.forEach( (img, arrayIndex) => {    
      if( arrayIndex == 0 ){
        sheet.getRange(currentRow, 24).setValue( img )
      } else { 
        sheet.insertRowAfter(currentRow)
        sheet.getRange(currentRow 1, 24).setValue( img )
      }
      
      // New rows in between were created
      currentRow  
    })

 });