Google Sheet formula to compile customer details and orders

Question

I'm using Google Form and Google Sheet for my small business. I give my customers a Google Form for them to key in their name, phone number and what their orders are and I can view all my customers' orders in the Google Sheet Form Responses.

An Example

However, sometimes the items list can be too many for me to scroll to the right and left so I made a new sheet and compiled their details and orders in a single cell using the code below:

=FormResponses1!B2&char(10)&
FormResponses1!C2&char(10)&
IF(FormResponses1!D2>0,FormResponses1!D2&" - "&FormResponses1!D1&char(10), "")&
IF(FormResponses1!E2>0,FormResponses1!E2&" - "&FormResponses1!E1&char(10), "")&
IF(FormResponses1!F2>0,FormResponses1!F2&" - "&FormResponses1!F1&char(10), "")&
IF(FormResponses1!G2>0,FormResponses1!G2&" - "&FormResponses1!G1&char(10), "")&
IF(FormResponses1!H2>0,FormResponses1!H2&" - "&FormResponses1!H1&char(10), "")&
IF(FormResponses1!I2>0,FormResponses1!I2&" - "&FormResponses1!I1&char(10), "")&
IF(FormResponses1!J2>0,FormResponses1!J2&" - "&FormResponses1!J1&char(10), "")&
IF(FormResponses1!K2>0,FormResponses1!K2&" - "&FormResponses1!K1&char(10), "")&
IF(FormResponses1!L2>0,FormResponses1!L2&" - "&FormResponses1!L1&char(10), "")&
IF(FormResponses1!M2>0,FormResponses1!M2&" - "&FormResponses1!M1&char(10), "")&
IF(FormResponses1!N2>0,FormResponses1!N2&" - "&FormResponses1!N1&char(10), "")&
IF(FormResponses1!O2>0,FormResponses1!O2&" - "&FormResponses1!O1&char(10), "")&

it goes on for at least 60 lines of IF statements to achieve the result as in the picture below

the result

The issue is that when i drag the formula down the "FormResponses1!D1" (the item name) changes to "FormResponses1!D2". So i have to change them individually. Other than that it works, but i was wondering if there is an easier way to achieve the same results. Thank you in advance!

Answer 1

to not change the frame of reference use $ in front of either column or row or both:

$D1
D$1
$D$1

F4 is the shortcut

try:

=INDEX(SUBSTITUTE(B2:B&CHAR(10)&C2:C&CHAR(10)&SUBSTITUTE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(D2:Z="",,D2:Z&"×-×"&SUBSTITUTE(D1:Z1, " ", "×"))),,9^9))), " ", CHAR(10)), "×", " "))

Answer 2

If you want to make this into a script, you can use the code below. It will change the formatting to look more like the image below, which I find more appealing. If you do use this option, don't forget to change the "numberOfItems" value.

function ConsolidateInfoTwo() {
  let sheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getSheetByName('YOUR_SHEET_NAME');
  let sheetDB = sheet.getRange(2,3,sheet.getMaxRows(),sheet.getMaxColumns()).getValues();
  let numberOfItems = 20; //Change this to however many you have
  let categoryAray = sheet.getRange(1,3,1,(numberOfItems+2)).getValues();
  let itemCategories = categoryAray[0];

for (i=0;i<sheet.getLastRow();i++)
 {
   var info = "";
   let currentData = sheetDB[i];
     for (h=0;h<(numberOfItems+2);h++) 
     {
         if (currentData[h] != '')
         {
        //If you don't want the items to come out as "Item 1: 3", you can change the order here
        info += (itemCategories[h]+": "+currentData[h] + "\n");
         }
      sheet.getRange(i+2,1).setValue(info);
    }
  }
}