COPY AND TRANSPOSE EACH COLUMN OF ONE SHEET TO DIFFERENT SHEETS

[Picloan Limited]

I  want to copy and transpose  column A,B,C from data sheet TO Natip sheet, regt sheet, provK sheet   ie  column A copy and transpose to Natip sheet, column B  copy and transpose to  regt sheet  , column C  copy and transpose to  provK sheet . when copying and transposing, empty cell should be ignored. please help me out

function trans1() {

  let ss = SpreadsheetApp.getActiveSpreadsheet();

  let shtIn = ss.getSheetByName("data");

  let  ssS = SpreadsheetApp.openById("17cRhuKDDqjCRFcMK");

  let shtOut = ssS.getSheetByName("natip");

  let responses = shtIn.getRange("A3:A").getValues();

  //responses is a 2d array that looks like this [[a],[b],[c]]

  

  //first transpose it

  let transResp = responses[0].map((resp,i)=>responses.map(r => r[i]))

  //we now have [[a,b,c]] 

  //BUT this is still 2d array.  the appendRow function needs a 1d array

  //So...  you can do one of two things...

  //1. just use the first row

  shtOut.appendRow(transResp[0])

  //2. you can use array.flat()

  //shtOut.appendRow(transResp.flat())

  let ss1 = SpreadsheetApp.getActiveSpreadsheet();

  let shtIn1 = ss1.getSheetByName("data");

  let  ssS1 = SpreadsheetApp.openById("17cRhuKDDqjCRFc");

  let shtOut1 = ssS.getSheetByName("regt");

  let responses1 = shtIn1.getRange("B3:B").getValues();

  //responses is a 2d array that looks like this [[a],[b],[c]]

  

  //first transpose it

  let transResp1 = responses[0].map((resp,i)=>responses1.map(r => r[i]))

  //we now have [[a,b,c]] 

  //BUT this is still 2d array.  the appendRow function needs a 1d array

  //So...  you can do one of two things...

  //1. just use the first row

  shtOut1.appendRow(transResp1[0])

  //2. you can use array.flat()

  //shtOut.appendRow(transResp.flat())

  let ss2 = SpreadsheetApp.getActiveSpreadsheet();

  let shtIn2 = ss2.getSheetByName("data");

  let  ssS2 = SpreadsheetApp.openById("17cRhuKDDqjCR");

  let shtOut2 = ssS2.getSheetByName("provK");

  let responses2 = shtIn2.getRange("C3:O").getValues();

  //responses is a 2d array that looks like this [[a],[b],[c]]

  

  //first transpose it

  let transResp2 = responses2[0].map((resp,i)=>responses.map(r => r[i]))

  //we now have [[a,b,c]] 

  //BUT this is still 2d array.  the appendRow function needs a 1d array

  //So...  you can do one of two things...

  //1. just use the first row

  shtOut2.appendRow(transResp2[0])

  //2. you can use array.flat()

  //shtOut.appendRow(transResp.flat())

}

[Alex]

You can try something like this

function initSettings_() {

  return [{

    bookId: '19WOUO63jy9yItRT88fsR6kd6YPWm-1kwkLBZwvEraV4',

    targetSheetName: 'natip',

    colIndex: 0

  }, {

    bookId: '19WOUO63jy9yItRT88fsR6kd6YPWm-1kwkLBZwvEraV4',

    targetSheetName: 'regt',

    colIndex: 1

  }, {

    bookId: '19WOUO63jy9yItRT88fsR6kd6YPWm-1kwkLBZwvEraV4',

    targetSheetName: 'provK',

    colIndex: 2

  }]

}

function run() {

  const data = SpreadsheetApp.getActive()

    .getSheetByName('data').getDataRange().getValues();

  const settings = initSettings_();

  settings.forEach(setts => {

    const pData = prepareData_(data, setts.colIndex);

    if (pData && pData.length)

      SpreadsheetApp.openById(setts.bookId)

        .getSheetByName(setts.targetSheetName)

        .appendRow(pData)

  })

}

/**

 * @param {any[][]} array

 */

function prepareData_(array, colIndex) {

  return array.map(row => row[colIndex])

    .filter(item => item !== '')

}

[Picloan Limited]

Thanks, after some modification, I ran the code but nothing happened. Am I missing. I only changed bookid. Notwithstanding, thanks

[Picloan Limited]

Sorry the mistake is from me. I wrongly copied bookid. I am very greatful it works great

[Alex]

I'm glad!

[Picloan Limited]

 Please Mr Alex,if I want to clear a content in sheet name "data" with this script:

 data.getRange("E16:P16").clearContent()

How can i incorporate into the scrip above. i try but it gives an error that data is not a  function. please help me

--
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/082390de-c7ba-4c30-813e-f45fdffbd8cdn%40googlegroups.com.

[Alex]

When do you want to do this? After all after the additions?

Let me guess

function run() {

  const sheet = SpreadsheetApp.getActive()

    .getSheetByName('data')

  const data = sheet.getDataRange().getValues();

  const settings = initSettings_();

  settings.forEach(setts => {

    const pData = prepareData_(data, setts.colIndex);

    if (pData && pData.length)

      SpreadsheetApp.openById(setts.bookId)

        .getSheetByName(setts.targetSheetName)

        .appendRow(pData)

  });

  sheet.getRange("E16:P16").clearContent();

}

[Picloan Limited]

Thanks Mr Alex

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e88af959-8df3-43bf-8542-9d13fadc5c66n%40googlegroups.com.