Home / Questions / 206728
Accepted
A. Cedano
Asked: 2020-10-23 03:31:47 +0800 CST 2020-10-23 03:31:47 +0800 CST

How can I get the data for an entire row in Google Sheets using Google Apps Script?

  • 772

I have created a trigger that sends me an email every time a Google Sheets sheet is updated.

But in the message I want to send the data that was written in each of the cells of that row that was updated.

In the code that I am implementing, I can only get the data of the specific cell that was updated, but not of the entire row.

My code is the following:

function onEdit(e) {
    var gSheet = SpreadsheetApp.getActiveSpreadsheet();
    var cell = gSheet.getDataRange().toString();
    var mEmail='[email protected]';
    var mSubject='Nuevo error 404';
    var mBody='Hoja modificada por: '+Session.getActiveUser()+
              '\nDatos: '+gSheet.getActiveCell().getValue();
    MailApp.sendEmail(mEmail, mSubject, mBody);
}

I was looking among the possibilities for a kind of method getActiveRow(), but I can't find it. How could I get all the values ​​of that row?

Current state of the code

Currently the code is like this:

function onEdit(e) {
    Logger.log("e");
    var gSheet = SpreadsheetApp.getActiveSpreadsheet();
    var cell = gSheet.getDataRange().toString();
    var numFila = e.range.rowStart; 
    var fila = gSheet.getRange(numFila + ":" + numFila);
    var valoresFila = fila.getValues();
    var mEmail='[email protected]';
    var mSubject='Nuevo error 404';
    var mBody='Hoja modificada por: '+Session.getActiveUser()+"\nDatos: "+gSheet.getActiveCell().getValue()+" c1: "+valoresFila[1]+" c2: "+valoresFila[2];
    MailApp.sendEmail(mEmail, mSubject, mBody);
}

When the sheet is updated I receive an email, but without data, something like this:

Hoja modificada por: [email protected]
Datos:
google-apps-script

2 Answers

  1. Best Answer

    Response to revision 2 , as far as added code is concerned.

    The problem is the indices on valoresFila[1]and valoresFila[2]. Assuming you want the values ​​of the cells in the active row that correspond to columns A and B, replace them with valoresFila[0][0]yvaloresFila[0][1]

    The above because it getValuesreturns an Array of Arrays and in JavaScript the indices of the Arrays are base 0. The first index corresponds to the row and the second to the column.

    Regarding the automatic execution of the function when a response is added from a Google form linked to the spreadsheet, instead of a trigger on Edituse a trigger on Form Submit.

    This is because activators on Editand on Change, whether simple or installable, are only activated by changes made by the user who has the spreadsheet open, not by changes made by another application, such as Google Forms, nor by code, nor by updates. automatic spreadsheet functions like IMPORTRANGE.

    IMPORTANT NOTES

    1. Google Apps Script handles two installable triggers on Form Submit, one for spreadsheet-bound projects and one for form-bound projects, each with an event object with particular properties. Please consult the properties of each in Events .
    2. Triggers like on Form Submitand time-drivencannot make use of methods like SpreadsheetApp.getActiveCell()because they do not emulate the user opening a spreadsheet and therefore there is no active cell, range or selection. However, in the case of spreadsheet-linked projects it can be used without problem SpreadsheetApp.getActiveSpreadsheet().
    3. In the case of using a trigger on Form Submiton a project linked to a form, if access to the linked spreadsheet is required, getDestinationId() could be used to get the id of the linked spreadsheet.

    Response to revision 1

    From the question...

    I was looking among the possibilities for a kind of method getActiveRow(), but I can't find it. How could I get all the values ​​of that row?

    There are several ways to get all the values ​​in a row.

    • Spreadsheet Reference Notation1:1
    • Use array notationmiArray[i]
    • UsegetRange(fila, columna, numeroFilas,numeroColumnas)

    For example, we can use

    var numFila = e.range.rowStart; 
var fila = gSheet.getRange(numFila + ":" + numFila);
var valoresFila = fila.getValues();

    where RowValues ​​will be an array of arrays of 1 x number of columns in the sheet.

    If you want to limit to only the rows of the data range, we can use getLastColumn() which in certain circumstances could be very slow, or use gSheet.getDataRange().getValues()[0].lengthto get the number of columns of the data range.

    • 4

  2. In macros, just below "run" in the menu, there is an active Project Triggers icon.

    this can help you with triggers according to conditions. I enclose an image of an example with sending form responses, execute the script of the selected function.

    enter image description here

    • 0