Exploring the capabilities of Google Apps Script for looping and generating output

Question

Exploring the capabilities of Google Apps Script for looping and generating output

I have two spreadsheets named rawData and processedData.

The content of rawData is as follows:

Status Title Options
Live Title1

Option1, Option2, Option3, Option4

Live Title2

Option1, Option2, Option3, Option4, Option5

Live Title3 Option1, Option2, Option3

The expected format for processedData should be:

Status Title Options
Live Title1 Option1
empty Title1 Option2
empty Title1 Option3
empty Title1 Option4
Live Title2 Option1
empty Title2 Option2
empty Title2 Option3
empty Title2 Option4
empty Title2 Option5
Live Title3 Option1
empty Title3 Option2
empty Title3 Option3

The placeholder empty indicates an empty cell in the spreadsheet.

I've attempted to use the provided code but couldn't achieve the desired output due to issues with the loops and my limited experience in this language.

function formatData() {
  // Code snippet here 
}

As a newcomer to this programming language, I apologize for posing what may seem like a basic question.

javascript arrays for-loop google-apps-script

Answer 1

Answer №1

Have you considered this adjustment? Keep in mind that there are multiple ways to modify the script.

Method 1:

Updated script:

After making modifications, your script will look like this. Specifically, focus on updating the for loop as shown below:

for (var row = 0; row < data.length; row++) {
  status = data[row][0];
  var title = data[row][1]; // Added
//    outputArray.push([status]); // Removed
  options = data[row][2].split(", "); // Modified
  for (var element = 0; element < options.length; element++) {
    if (element == 0) { // Added and modified
      outputArray.push([status, title, options[element]]);
    } else {
      outputArray.push(["", title, options[element]]);
    }
  }
}

Method 2:

Alternatively, consider this different approach for modifying the script. Make the following adjustments when implementing this method:

Updated script:

From:

// Initialise an array that will hold the output
var outputArray = [];

// Name a variable to hold the data from each set of options
var options;

var status;

// Start looping through the data
for (var row = 0; row < data.length; row++) {

  status = data[row][0];

  outputArray.push([status]);

  // Split the options into an array: "Option1, Option2, Option3" ---> [Option1, Option2, Option3]
  options = data[row][1].split(", ");

  // Loop through the array of split options and place each of them in a new row
  for (var element = 0; element < options.length; element++) {

    outputArray.push([data[row][0], // Place the title in a new row
                      options[element]]); // Place one option in the 2nd column of the row

  } // Options loop ends here

} // Data loop ends here

To:

var outputArray = data.reduce(function(ar1, e) {
  var h1 = e.splice(0, 1)[0];
  var h2 = e.splice(0, 1)[0];
  var options = e[0].split(", ");
  return ar1.concat(options.reduce(function(ar2, f, j) {
    if (f) ar2.push(j == 0 ? [h1, h2, f] : ["", h2, f]);
    return ar2;
  }, []));
}, []);

The end result aligns with Method 1.

Note:

It appears that the header "Status, Title, Options" is missing in your script. Would you like to include it?

Reference:

reduce()

If I have misinterpreted your question and provided an incorrect solution, I apologize for the confusion.

Answer 2

Have you considered this adjustment? Keep in mind that there are multiple ways to modify the script.

Method 1:

Updated script:

After making modifications, your script will look like this. Specifically, focus on updating the for loop as shown below:

for (var row = 0; row < data.length; row++) {
  status = data[row][0];
  var title = data[row][1]; // Added
//    outputArray.push([status]); // Removed
  options = data[row][2].split(", "); // Modified
  for (var element = 0; element < options.length; element++) {
    if (element == 0) { // Added and modified
      outputArray.push([status, title, options[element]]);
    } else {
      outputArray.push(["", title, options[element]]);
    }
  }
}

Method 2:

Alternatively, consider this different approach for modifying the script. Make the following adjustments when implementing this method:

Updated script:

From:

// Initialise an array that will hold the output
var outputArray = [];

// Name a variable to hold the data from each set of options
var options;

var status;

// Start looping through the data
for (var row = 0; row < data.length; row++) {

  status = data[row][0];

  outputArray.push([status]);

  // Split the options into an array: "Option1, Option2, Option3" ---> [Option1, Option2, Option3]
  options = data[row][1].split(", ");

  // Loop through the array of split options and place each of them in a new row
  for (var element = 0; element < options.length; element++) {

    outputArray.push([data[row][0], // Place the title in a new row
                      options[element]]); // Place one option in the 2nd column of the row

  } // Options loop ends here

} // Data loop ends here

To:

var outputArray = data.reduce(function(ar1, e) {
  var h1 = e.splice(0, 1)[0];
  var h2 = e.splice(0, 1)[0];
  var options = e[0].split(", ");
  return ar1.concat(options.reduce(function(ar2, f, j) {
    if (f) ar2.push(j == 0 ? [h1, h2, f] : ["", h2, f]);
    return ar2;
  }, []));
}, []);

The end result aligns with Method 1.

Note:

It appears that the header "Status, Title, Options" is missing in your script. Would you like to include it?

Reference:

reduce()

If I have misinterpreted your question and provided an incorrect solution, I apologize for the confusion.

Exploring the capabilities of Google Apps Script for looping and generating output

Answer №1

Method 1:

Updated script:

Method 2:

Updated script:

Note:

Reference:

Similar questions

Do AJAX requests make Cross-site scripting attacks more significant?

Having trouble saving text in a textbox?

What is the connection between importing and using destructuring in ES6 syntax?

Guide on assigning a callback function in JavaScript

Performance challenges with an AngularJS application that uses pagination

How can I ensure a header is displayed on each page by utilizing CSS or JavaScript/jQuery?

Looking to determine if a specific element is assigned multiple class names

Angular filter is causing an error message saying "Unable to interpolate," but the filter is functioning as expected

The "as" property in NextJS Link does not properly reload the page when opened

Detecting Scroll on Window for Specific Element using Jquery

Develop a fresh class by inheriting from HTMLDivElement and expanding its prototype

Guide to sending a post request with parameters in nuxt.js

The Concept of Long Polling and How it Impacts Server Function

Retrieve and manipulate the HTML content of a webpage that has been loaded into a

What is the proper way to utilize --legacy-peer-deps or enforce in a vite build?

I'm not sure how I can retrieve the pollId from a ReactJS poll

Stop the recurrence of multiple clicks by incorporating a Bootstrap modal popup confirmation

Is it feasible to activate a function when certain Vue data elements are altered?

Error: Undefined object while trying to access 'injection' property

Is it possible for Angular2 to map a lone JSON object?