How to use Class range when coding in apps script

I’m building a Slack Bot for my internship, and I can get it to send direct messages to a private channel, and connect through the API. But my current problem is getting the algorithm to take the information in a spreadsheet and form a message with that data in the channel. I found a tutorial on youtube, and their code looks like this, I need to understand what this code is doing, please help!

function getLatestMetrics(){

    const ss = SpreadsheetApp.openById(YOUR_SPREADSHEET_ID)
    const sheet = ss.getSheetByName(YOUR_SHEET_NAME)
    const latest_metrics = sheet.getRange(6,1,8,7).getValues();
    const metric_deltas = sheet.getRange(2,4,2,4).getValues();
    const metric_array_positions = {

      queries: 3,
      first_page: 4,
      impressions: 5,
      clicks: 6
    }

    const metrics = {
      period: {
        week: {
          start: Utilities.formatDate(latest_metrics[0][1],"EST", "yyyy-MM-dd"),
          end: Utilities.formatDate(latest_metrics[0][2],"EST", "yyyy-MM-dd")
        },

        month: {
          start: Utilities.formatDate(latest_metrics[3][1],"EST", "yyyy-MM-dd"),
          end: Utilities.formatDate(latest_metrics[0][2],"EST", "yyyy-MM-dd")
        }
      },

      queries: {
        this_week: latest_metrics[0][metric_array_positions.queries],
        last_week: latest_metrics[1][metric_array_positions.queries],
        this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.queries),
        last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.queries),
        wow_delta: Math.round(metric_deltas[0][0]*100) + '%',
        mom_delta: Math.round(metric_deltas[1][0]*100) + '%'
      },


      first_page: {
        this_week: latest_metrics[0][metric_array_positions.first_page],
        last_week: latest_metrics[1][metric_array_positions.first_page],
        this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.first_page),
        last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.first_page),
        wow_delta: Math.round(metric_deltas[0][1]*100) + '%',
        mom_delta: Math.round(metric_deltas[1][1]*100) + '%'
      },


      impressions: {
        this_week: latest_metrics[0][metric_array_positions.impressions],
        last_week: latest_metrics[1][metric_array_positions.impressions],
        this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.impressions),
        last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.impressions),
        wow_delta: Math.round(metric_deltas[0][2]*100) + '%',
        mom_delta: Math.round(metric_deltas[1][2]*100) + '%'
      },


      clicks: {
        this_week: latest_metrics[0][metric_array_positions.clicks],
        last_week: latest_metrics[1][metric_array_positions.clicks],
        this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.clicks),
        last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.clicks),
        wow_delta: Math.round(metric_deltas[0][3]*100) + '%',
        mom_delta: Math.round(metric_deltas[1][3]*100) + '%'
      }
    }
    return metrics
}
function sumSheetValues(data, array_row_start, array_row_end, array_column) {
    let result = 0
    for(let i = array_row_start; i <= array_row_end; i++) {
      result += data[i][array_column]
    }
    return result
}

Answer

Getting data from Spreadsheet and iterating over it

function getSpreadsheetData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
  Logger.log(JSON.stringify(vs));
  vs.forEach((row,i) => {
    //iterate through rows
    row.forEach((col,j) => {
      //iterate through columns
    })
  })
}

Data:

COL1	COL2	COL3	COL4	COL5
12	3	19	14	5
9	6	15	4	15
13	7	9	14	6
17	17	7	11	0
16	4	18	14	17

Execution log
7:44:48 AM  Notice  Execution started
7:44:49 AM  Info    [[12,3,19,14,5],[9,6,15,4,15],[13,7,9,14,6],[17,17,7,11,0],[16,4,18,14,17]]
7:44:50 AM  Notice  Execution completed

Advertisement

Answer

Getting data from Spreadsheet and iterating over it