Update Cell using column and row using app script-CodePudding

I am trying to do a forEach on the objects in "result" below, lookup up the cell value in the "sumActual" sheet by "category" and "startofDate", then sum the two values and set the new summed value back to the cell in "sumActual".

For example in the sample sheet I'd expect the following in the sample sheet below.

B3: 100 (90 10)

C3: 100 (80 20)

B4: 1600 (1500 100)

C4: 266 (66 200)

I'm just not sure how to find the cell value by where the "category" and "startofDate" interesct.

Thanks in advance for any support.

{ result: 
 [ { category: 'Credit Card Fees',
   month: 'January',
   year: 2021,
   amount: 90,
   startofMonth: '01/01/2021' },
 { category: 'Credit Card Fees',
   month: 'February',
   year: 2021,
   amount: 80,
   startofMonth: '02/01/2021' },
 { category: 'Processing',
   month: 'January',
   year: 2021,
   amount: 1500,
   startofMonth: '01/01/2021' },
 { category: 'Processing',
   month: 'February',
   year: 2021,
   amount: 66,
   startofMonth: '02/01/2021' } ] }

Sample Sheet

After Screenshot

CodePudding user response：

I believe your goal is as follows.

Using the value in your question, you want to convert from your upper image to your bottom image using Google Apps Script.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  // This value is from your question.
  const { result } = {
    result:
      [{
        category: 'Credit Card Fees',
        month: 'January',
        year: 2021,
        amount: 90,
        startofMonth: '01/01/2021'
      },
      {
        category: 'Credit Card Fees',
        month: 'February',
        year: 2021,
        amount: 80,
        startofMonth: '02/01/2021'
      },
      {
        category: 'Processing',
        month: 'January',
        year: 2021,
        amount: 1500,
        startofMonth: '01/01/2021'
      },
      {
        category: 'Processing',
        month: 'February',
        year: 2021,
        amount: 66,
        startofMonth: '02/01/2021'
      }]
  };

  const obj = result.reduce((o, { category, amount, startofMonth }) => (o[startofMonth   category] = amount, o), {});
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sumActual");
  const [[, ...header], , ...values] = sheet.getDataRange().getValues();
  const res = values.map(([h, ...v]) => v.map((f, j) => {
    const key = Utilities.formatDate(header[j], Session.getScriptTimeZone(), "MM/dd/yyyy")   h;
    return obj[key] ? obj[key]   f : f;
  }));
  sheet.getRange(3, 2, res.length, res[0].length).setValues(res);
}

When this script is run, the cells of sheet "sumActual" is updated using the value in your question. The goal in the bottom image in your question can be achieved from the upper image.

Note:

This sample script is for your sample image. So when your actual situation is different from it, this script might not be able to be used. Please be careful about this.

Sample script:

Note:

References: