user2033763 user2033763 -4 years ago 104
Javascript Question

Need google apps script to iterate through an array

I have modified this code I found but i cannot figure out how to get it to run for the whole spreadsheet.

the modified code copies a spreadsheet, renames it based on another spreadsheet and then files the document in the appropriate folder.

function generateMonthlyReport() {

var data = SpreadsheetApp.openById(CUSTOMER_SPREADSHEET);

// Fetch variable names
// they are column names in the spreadsheet
var sheet = data.getSheets()[0];
var columns = getRowAsArray(sheet, 1);

Logger.log("Processing columns:" + columns);

var customerData = getRowAsArray(sheet, CUSTOMER_ID);
Logger.log("Processing data:" + customerData);

// Assume first column holds the name of the customer
var customerName = customerData[1];
var customerCityState = customerData[2];
SpreadsheetApp.getActiveSheet().getSheetId()
var target = createDuplicateDocument(SOURCE_TEMPLATE, "CSMR - " + customerName + ", " + customerCityState );
Logger.log("Created new document");
}

Answer Source

not sure I understood exactly what you wanted to get as a final result but here is how the loop could be built, it will create a doc for each row of data.

function generateMonthlyReport() {
  var data = SpreadsheetApp.getActiveSpreadsheet();
  // Fetch variable names
  // they are column names in the spreadsheet
  var sheet = data.getSheets()[0];
  var columns = getRowAsArray(sheet, 1);
  for(var customer=2;customer<sheet.getLastRow();customer++){
    Logger.log("Processing row:" + customer);
    var customerData = getRowAsArray(sheet, customer);  
    Logger.log("Processing data:" + customerData);
    // Assume first column holds the name of the customer
    var customerName = customerData[0];
    var customerCityState = customerData[2];
    SpreadsheetApp.getActiveSheet().getSheetId()
    //  var target = createDuplicateDocument(SOURCE_TEMPLATE, "CSMR - " + customerName + ", " + customerCityState );
    Logger.log("Created new document for customer "+customer+' ctitle = CSMR - ' + customerName + ', ' + customerCityState); 
  }
}

function getRowAsArray(sheet, n){
  var array = [];
  var data = sheet.getRange(n,1,1,sheet.getLastColumn()).getValues();
  for(var c=0;c<data[0].length;c++){
    array.push(data[0][c]);
  }
  return array;
}

Logger of the test + test sheet below : enter image description here

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download