Apps
Google Sheets
19 min
learn how to connect engini to google sheets using engini’s google sheets activities, you can create spreadsheets, update values, append data, and manage your sheets effortlessly to get started with the google sheets app, create a free account at google com or use your organizational google workspace account getting started with google sheets prerequisites a google sheets account add a connection to google sheets in engini enter your engini account at https //app engini io/ navigate to connections page by clicking on the connections on the left sidebar or by clicking https //app engini io/connections click on the new integration option located at the topbar choose google sheets option from the available applications enter the details in the “add connection” form note there are two different authentication methods available, and the required fields differ depending on the option you choose please follow the relevant instructions based on your selected method oauth2 this method uses a predefined authentication flow you don’t need to provide a client id or client secret, you simply sign in using your account connection name enter unique identifier for the connection within engini authentication method select oauth2 click on the “sign in with google” button sign into your google account fields automatically populated post connecting to a google account include access token a token for authorization to access google services refresh token a token used to obtain a new access token when the current one expires click on the save settings button to save the connection custom oauth2 this method requires you to configure your own oauth2 credentials you must provide both a valid client id and client secret obtained from your google cloud console project connection name choose a unique identifier for the connection within engini authentication method select custom oauth2 when connecting to google services using a custom oauth client to learn more about this authentication, enter https //app archbee com/public/preview ixqqblwfxopjg0nave78y/preview pge7hsn11qgbublnn0zz#yhkxl client id enter the client id from the oauth 2 0 credentials in the google cloud project c lient secret enter the client secret from the same set of oauth 2 0 credentials in the google cloud project access token & refresh token automatically populated post connecting to a your google account sign in with google click sign in with google and log in with the google account used to create the project in google cloud this ensures access to the correct oauth credentials and permissions click on the save settings button to save the connection actions create a spreadsheet this activity creates a new google sheets spreadsheet title enter the title of the new spreadsheet add field sheets\[] – enter an array of sheet objects to define the initial sheets the spreadsheet will contain initialize sheet array this activity initializes an array of sheet objects to be used when creating a spreadsheet variable name enter the name of the variable that will hold the array of sheet objects add field index – set the position where the new sheet will appear in the spreadsheet title – enter the title for the new sheet being appended to the array append to sheet array this activity adds a new sheet object to an existing sheet array variable \ choose the sheet array variable where the new sheet will be appended fields index – set the position where the new sheet will appear in the spreadsheet title – enter the title for the new sheet being appended to the array range field the range field specifies the cells in the sheet to be updated or added to the value in this field must be written using a1 notation, which defines the location of cells or ranges within the sheet this notation combines column letters and row numbers to identify specific cells, ranges, or entire columns/rows examples of a1 notation for the range field (sheet name mysheet) mysheet!a\ a – refers to all cells in the first column of mysheet mysheet!a1\ b2 – refers to a specific range of cells in mysheet (first two rows and columns) mysheet!1 2 – refers to all cells in the first two rows of mysheet mysheet refers to all cells in the sheet named mysheet get a spreadsheet this activity retrieves metadata about a specified spreadsheet in google sheets, such as its properties, sheets, and other details s pread sheet id enter the spreadsheet id to identify which google sheets file to retrieve filter you can specify which records will be returned from the search results by utilizing the “add filter” button choose from the drop down the field you want to filter by select the condition you want the record to meet for the selected field you can populate the field in one of the following options static value – number / string when using strings, you need to surround them with single quotes (‘) property value from a previous activity (using the tooltip that opens when clicking on the field) expression – using https //app archbee com/public/preview ixqqblwfxopjg0nave78y/preview o3kkbgi5xnqf yvnuqkcv#ca 5p and/or previous activity properties and/or static values (using the tooltip that opens when clicking on the field) repeat steps a d for all the fields you want to populate reset choose the filters from scratch add field include grid data – choose whether to include the data within the spreadsheet cells (true/false) ranges – specify one or more ranges of cells to retrieve get sheet / cell enter the spreadsheet id to identify the google sheets file from which to retrieve the sheet or cell spreadsheetid enter the spreadsheet id to identify the google sheets file range specify the sheet name and the cell range to retrieve data from major dimension choose whether to operate on rows or columns of the specified range vallue render option select whether to return raw, unformatted data or display values as seen in the spreadsheet add a sheet this activity adds a new sheet to an existing google sheets spreadsheet spreadsheet id enter the spreadsheet id where the new sheet will be added properties title – specify the title for the new sheet you want to add add property index – set the index where the new sheet will appear within the spreadsheet (starts from 0) add a row/column/cell this activity allows you to add a new row, column, or cell to a specified location in a sheet spread sheet id enter the spreadsheet id where the row, column, or cell will be added range specify the range in the sheet where the new row, column, or cell will be added major dimension choose whether the activity will affect rows or columns (rows/columns) values \[] provide a 2d array (array of arrays) where each inner array represents a row (or column, based on the major dimension) these values will be added as new rows, columns, or cells in the specified range ensure the array size matches the intended addition to avoid errors value input option choose how the input values should be interpreted (raw/user entered) add field include values in response – set whether the response should include the inserted values (true/false) insert data option – specify how the data should be inserted into the existing sheet (overwrite/insert rows) response date time render option – choose how date and time values should be rendered in the response (formatted string) response value render option – choose how the values in the response should be returned (formatted value/unformatted value/formula) update row/column/cell this activity updates the content in a specific range of rows, columns, or cells in a google sheets spreadsheet spread sheet id enter the spreadsheet id where the rows, columns, or cells will be updated range specify the range of the sheet where the update will happen major dimension choose whether the update will operate on rows or columns values\[] provide a 2d array (array of arrays) where each inner array represents a row (or column, depending on the selected major dimension) the data in these arrays will be written to the specified range in the sheet ensure the array size matches the range size to avoid errors value input option define how the input values should be interpreted (raw/user entered) add field include values in response – choose whether to include the updated values in the response response date time render option – choose how date and time values should be displayed in the response (formatted string) response value render option – choose how the values should be returned in the response (formatted value/unformatted value/formula) clear cell/row/column/sheet this activity clears the content in a specific range of cells, entire rows, columns, or even the entire sheet in a google sheets spreadsheet spread sheet id enter the spreadsheet id where the rows, columns, or cells will be cleared range enter the specific range of cells, rows, columns, or the entire sheet to clear delete sheet this activity deletes an entire sheet from a google sheets spreadsheet spreadsheet id enter the spreadsheet id where the sheet will be deleted properties sheet id – enter the sheet id that will be deleted from the spreadsheet delete row/column this activity deletes entire rows or columns from a google sheets spreadsheet spread sheet id – enter the spreadsheet id where the rows or columns will be deleted properties sheet id – enter the sheet id within the spreadsheet where the rows or columns will be deleted dimension – enter whether you want to delete rows or columns (rows/columns) start index – enter the starting index of the row or column range to be deleted (indexing starts from 0) end index – enter the ending index of the row or column range to be deleted (the index is excluded from the deletion) rename sheet this activity allows to rename a specific sheet within a google sheets spreadsheet spread sheet id enter the id of the spreadsheet that contains the sheet you want to rename properties sheet id – enter the id of the sheet that you want to rename title – enter the new name for the sheet copy a sheet this activity copies a sheet from one google sheets spreadsheet to another, or within the same spreadsheet source spread sheet id enter the id of the spreadsheet that contains the sheet you want to copy sheet id enter the id of the sheet you want to copy from the source spreadsheet add field destination spreadsheet id enter the id of the spreadsheet where the sheet will be copied