Divide data and copy other cells in google sheets or excel

Question

Hello, I need your assistance. In a Google Sheet, I have data that is dispersed across several cells. Cell 1 contains a name, Cell 2 contains many lines of data, and Cell 3 contains a name. I need to divide the data in Cell 2 into different vertical cells by copying the data from Cells 1 and 3 as shown in the example.

]

I tried transpose but it failed

Answer 1

Try this :

const Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`YourSheetName`)

function dataHandler() {

  const dataRange = Sheet.getDataRange()
                         .offset(1, 0)
  
  const data = dataRange.getValues()

  const newValues = data.flatMap(([ name, location, addresses, phone]) => {
    
    const addressList = addresses.split(`\n`)

    return location.split(`\n`)
                   .map((item, index) => {
      return [
        name,
        (addressList[index]) ? addressList[index] : ``,
        item,
        phone
      ]
    })

  })

  dataRange.clearContent()

  Sheet.getRange(2, 1, newValues.length, newValues[0].length)
       .setValues(newValues)

}