Issue arises when the function in a for loop disrupts the loop from functioning correctly

Question

Issue arises when the function in a for loop disrupts the loop from functioning correctly

This particular script is designed to scan through a sheet for currency values and create key/value pairs for a JSON based on the column and row headers. It basically detects a currency value, then traces back row and column numbers until it reaches a non-currency, non-blank value which is used along with the currency to form pairs. There are three functions involved: one to enable the second by identifying if a value is currency, the second function builds the object, and the third one iterates through the sheets and constructs the object for each sheet that meets specific criteria.

Each sheet is named following a pattern like "name_name_type_location", and the specified criteria is anything with the type "AF".

The problem encountered is when calling tariffObject(sheets[i]) within publishedSheets(), it appears to disrupt the for loop, resulting in only receiving the first sheet object even though there are more than four sheets meeting the criteria. Removing the tariffObject(sheets[i]) call resolves the issue. There seems to be something causing this loop interruption.

How can this be resolved?

function isCurr(x){
  var regex = /^[$]\d+(?:\.\d{0,2})$/;
  if (regex.test(x)){
    return true;
  }
  else{
    return false;
  }
}

function tariffObject(sheet){
  // Function code...
}

function publishedSheets(){
  // Function code...
}

https://i.sstatic.net/tXrY7.jpg

javascript json for-loop google-apps-script

Answer 1

Answer №1

Give this a try:

function generateJsonFromSheet() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const [headers, ...dataRows] = sh.getDataRange().getValues();
  let jsonObj = { rows: [] };
  dataRows.forEach((row, i) => {
    row.forEach((cell, j) => {
      if (j == 0) {
        jsonObj[cell] = {};
        jsonObj.rows.push(cell);
      } else {
        jsonObj[dataRows[i][0]][headers[j]] = cell;
      }
    });
  });
  Logger.log(JSON.stringify(jsonObj));
  headers.shift();
  let output = '';
  jsonObj.rows.forEach((r, i) => {
    output += r + "={";
    headers.forEach((h, j) => {
      if (j > 0) output += ',';
      output += h + "=" + jsonObj[r][h];
    });
    output += '}\n';
  });
  Logger.log(output);

}

Data in Sheet1:

	H1	H2	H3	H4	H5	H6	H7	H8	H9	H10
R1	1	11	21	31	41	51	61	71	81	91
R2	2	12	22	32	42	52	62	72	82	92

Output:

R1={H1=1,H2=11,H3=21,H4=31,H5=41,H6=51,H7=61,H8=71,H9=81,H10=91}
R2={H1=2,H2=12,H3=22,H4=32,H5=42,H6=52,H7=62,H8=72,H9=82,H10=92}
<!-- Additional outputs would follow for each row -->

The provided output is generated from the object shown below:

{"rows":["R1","R2",...], <Details of the individual rows> }

Answer 2

Give this a try:

function generateJsonFromSheet() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const [headers, ...dataRows] = sh.getDataRange().getValues();
  let jsonObj = { rows: [] };
  dataRows.forEach((row, i) => {
    row.forEach((cell, j) => {
      if (j == 0) {
        jsonObj[cell] = {};
        jsonObj.rows.push(cell);
      } else {
        jsonObj[dataRows[i][0]][headers[j]] = cell;
      }
    });
  });
  Logger.log(JSON.stringify(jsonObj));
  headers.shift();
  let output = '';
  jsonObj.rows.forEach((r, i) => {
    output += r + "={";
    headers.forEach((h, j) => {
      if (j > 0) output += ',';
      output += h + "=" + jsonObj[r][h];
    });
    output += '}\n';
  });
  Logger.log(output);

}

Data in Sheet1:

	H1	H2	H3	H4	H5	H6	H7	H8	H9	H10
R1	1	11	21	31	41	51	61	71	81	91
R2	2	12	22	32	42	52	62	72	82	92

Output:

R1={H1=1,H2=11,H3=21,H4=31,H5=41,H6=51,H7=61,H8=71,H9=81,H10=91}
R2={H1=2,H2=12,H3=22,H4=32,H5=42,H6=52,H7=62,H8=72,H9=82,H10=92}
<!-- Additional outputs would follow for each row -->

The provided output is generated from the object shown below:

{"rows":["R1","R2",...], <Details of the individual rows> }

Issue arises when the function in a for loop disrupts the loop from functioning correctly

Answer №1

Similar questions

Is there a way to prevent the conversion of "u003c" to "<" when converting a JSON string to an object?

Exploring a List with APL Integration through an HTTP-Endpoint for Alexa

Issue: A request is not pending for flushing during the testing of an AngularJs service

I am looking to obtain assistance through denomongo for guidance

What could be causing my code to retrieve duplicate items every time the page is loaded?

I can't find my unit test in the Test Explorer

The limit of update depth when using useState with an array generated from a map operation

Mocha test failing to trigger function execution

Twitter API causing issues with setTimeout function in Node.js

Inheritance of Mongoose methods across all Schemas using the Schema method

Refreshing ng-repeat dynamically with $http without reloading the page

Exploring JSON data using Python - Tallying the number of cars and people

The Angular component router-outlet is not recognized as a known element

What is the best way to insert a hyperlink into the header of a column in a React table that is built using Material

Utilize dojo to manually trigger a window resize event

Potential risk of memory leakage when utilizing the CanvasRenderer

After the update, Material UI is causing a TypeError by throwing an error stating that it cannot read the property 'muiName' of an

Is there a way to eliminate duplicates from an array in JavaScript by utilizing a set and placing each element in an li tag? Here is the code

There was an issue parsing the JSON Array from the response string received from the EC2 instance

Decoding GeoJSON: Extracting a feature from an array