Why?
A common example would be that when your Google Apps Script project performs a task for you, such as enrolling students to a Classroom course, inviting Guardians or sending out emails, you might want to write a confirmation back to a column on the sheet to note success or failure.
How?
Paste the following two lines into your script:
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange(1, 1, yourDataArray.length, 4)
range.setValues(yourDataArray);Notes
The first line assumes that you’re going to write onto the currently selected sheet of the spreadsheet. This is safe when there is only one sheet in the spreadsheet, but if there are multiple sheets you can replace it with
SpreadsheetApp.getActive().getSheetByName('Name-of-the-sheet');The second line defines the range of cells that you are going to write your values to. The first two parameters define the row and column index of the top-left most cell of the range. The 1, 1 above would correspond to the cell A1, whereas 4, 2 would be B4. (Row and column indices start at 1, unlike javascript array indices, which start at 0 for the first element.) The second two parameters are the number of rows and the number of columns in the range. This must match the dimensions of the javascript array that is holding the data. You usually know how many columns this is (e.g. 4 in the example above), but you often don’t know how many rows your script is working through; the yourDataArray.length part counts this for you.
The final line writes the values stored in the variable yourDataArray. Note that this needs to be a two-dimensional array of the form [ [row 1 col 1, row 1 col 2, row 1 col 3, row 1 col 4], [row 2 col 1, row 2 col 2, row 2 col 3, row 2 col 4], … ]. This is most easily achieved by defining an empty array:
let yourDataArray = [];and then having a loop that cycles through the rows, “pushing” an array into yourDataArray on each pass through the loop. E.g.
yourDataArray.push([name, form, email, status]);where each of the four elements of the array are variables your script is generating during the loop.
References
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
https://developers.google.com/apps-script/reference/spreadsheet/sheet
https://developers.google.com/apps-script/reference/spreadsheet/range#setValues(Object)
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/push
