I have a sheet named 'Members' in my google sheets doc, from which I want to get the last 5 rows' data in JSON format using Google Apps Script. I only have two columns in my sheet, so the JSON won't be big.

I found out apps script has a getlastrow() function, which returns the last row of the sheet - will this function help me in my case? I am new to google apps script so I am unsure where to begin... How do I do this? Please guide... Thanks!

CodePudding user response：

About I want to get the last 5 rows' data in JSON format using Google Apps Script., if your expected JSON format is like [{"header1": "value1", "header2": "value2"},,,], how about the following sample script.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet including "Members" sheet. And, please run this function. By this, you can see the last 5 rows and the array including JSON data for each row in the log.

function myFunction() {
  const sheetName = "Members"; // Please set the sheet name.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const [header, ...values] = sheet.getDataRange().getValues();

  const last5Rows = values.slice(-5); // This is last 5 rows.
  console.log(last5Rows); // You can see this value in the log.

  const obj = last5Rows.map(r => header.reduce((o, h, j) => (o[h] = r[j], o), {})); // This is an array including JSON data of each row.
  console.log(obj); // You can see this value in the log.
}

• When this script is run, the values are retrieved from "Members" sheet. And, the last 5 rows are retrieved, and an array including JSON data for each row is created.

Note:

• Unfortunately, I'm not sure whether I could correctly understand JSON format from your question. So, if the output result of above sample script was not your expected value, can you provide the sample input and output values? By this, I would like to modify the script.

References:

• map()
• reduce()