Home > OS >  Google Sheet formula to compile customer details and orders
Google Sheet formula to compile customer details and orders

Time:04-14

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.

enter image description here

CodePudding user response:

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.

mock script result

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);
    }
  }
}
  • Related