[Forminator Pro] Google sheets API

Hello,
When i use Google sheets API , new values appear at the bottom of the sheet. in a way that the new values appear at the top of the sheet?
https://stackoverflow.com/questions/48987861/google-sheets-api-append-method-last-on-top

  • Jonjon

    Hello there, Finca.

    Indeed what you desire is not possible, that is: to prepend data to spreadsheet using the Google API directly.

    There are good news however, meaning there is a workaround which consists of adding a trigger to your Spreadsheet to run after each edit and automatically sort the column. That means if you have a date or any other alphanumeric column in order, this could work for you. Please follow the steps below to enable this:

    1. Go to your Spreadsheet and open the script editor

    2. On the editor, adapt this script to your needs.

    /**
    * Sorts a sheet by a column in descending order.
    * To sort automatically when a form response is received, set up a trigger like this:
    * Tools > Script editor > Resources > Current project's triggers > add new one >
    * sortFormResponsesSheet > From spreadsheet > On form submit > Save
    * see https://productforums.google.com/d/topic/docs/V_UVuwX_UFY/discussion
    * version 1.0, written by --Hyde 5 October 2014
    */
    
    var sheetToSort = "sheet 1"; // replace this with whichever sheet you want to sort automatically
    var columnToSortBy = 1; // column A = 1, B = 2, etc.
    var rangeToSort = "A1:T";
    
    function onEdit() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var editedCell = sheet.getActiveCell();
      if (editedCell.getColumn() == columnToSortBy && sheet.getName() == sheetToSort) {
        sortFormResponsesSheet();
      }
    }
    
    function sortFormResponsesSheet() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetToSort);
      var range = sheet.getRange(rangeToSort);
      range.sort( { column : columnToSortBy, ascending: false } );
    }

    3. Set the Trigger to run after each edit or change and Save it.

    Hope it helps you, and let us know if you need anything else, I'll be glad to help if I can.

    Best Regards,
    Jon

  • Jonjon

    Hi there, Finca.

    Hope you are doing great :slight_smile:

    Unfortunately, the onEdit() trigger on Google spreadsheets, does not recognize a new row as an edit. But you can at least add onOpen() trigger as well, so anytime a document is refreshed or opened, it will re-sort rows. So you can, for example, add the code below to the function.

    function onOpen(e) {
    
        sortFormResponsesSheet();
    
    }

    You can find more detailed in Google's official documentation on the link below.

    https://developers.google.com/apps-script/guides/triggers/

    Hope to have helped and have a great day ahead.

    Best,
    Jon

  • Jonjon

    Finca.

    I have it like this and it works for me, after page refresh:

    /**
    * Sorts a sheet by a column in descending order.
    * To sort automatically when a form response is received, set up a trigger like this:
    * Tools > Script editor > Resources > Current project's triggers > add new one >
    * sortFormResponsesSheet > From spreadsheet > On form submit > Save
    * see https://productforums.google.com/d/topic/docs/V_UVuwX_UFY/discussion
    * version 1.0, written by --Hyde 5 October 2014
    */
    
    var sheetToSort = "sheet 1"; // replace this with whichever sheet you want to sort automatically
    var columnToSortBy = 1; // column A = 1, B = 2, etc.
    var rangeToSort = "A2:T";
    
    function onEdit(e) {
    
        sortFormResponsesSheet();
    
    }
    
    function onOpen(e) {
    
        sortFormResponsesSheet();
    
    }
    
    function sortFormResponsesSheet() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheetByName(sheetToSort)
      var range = sheet.getRange(rangeToSort);
    
     // Sorts by the values in column 2 (B)
     range.sort({column: columnToSortBy, ascending: false});
    
    }

    For me it works after page refresh.

    Hope to have helped.

    Best,
    Jon

  • Jonjon

    Hi there Finca.

    I'm glad you got to make it work.

    About your first question. Well, the trigger only runs on page refresh for me, that's how google spreadsheets edit event works. It does not consider a new inserted row and edit event. you could try updating a dummy field and see if that runs the trigger.

    About your second question, I did not set any date format anywhere, Google just used their own default format. However, I believe you can use custom formats you could search google documentation. I suggest to start here: https://support.google.com/docs/answer/56470?co=GENIE.Platform%3DDesktop&hl=en

    Hope to have helped,

    Best,
    Jon

  • Jonjon

    Hi there Finca.

    Sorry, you are right. In the example I shared with you concerning to the google sheet trigger, it does not create the date-time column automatically. That is an option of course but there is an easier option.

    So I have tested this successfully and there is an automatic date column to Forminator form that you could use to achieve what you want. You can follow the steps below to do it:

    1. Add a hidden field to your form, give it a label name then select type date (mm/dd/YY) from the Drop-down list. Click Done and Save the Form. See screenshots below:

    2. Then on the google sheet trigger select the column number that will have the date column so you can order by it. See my example below:

    Please NOTE: The column to sort by must match the date column and if this form is old you might need to add old dates manually.

    If you desire to order not only by date but also by hours, minutes and seconds this is not supported by default as of now. But if that's what you want you will need to edit the source code to include those values as well in your automatically generated date column. You may edit the file below:

    wp-content/plugins/forminator/library/fields/hidden.php

    Search for this lines: (On my file it was line 182)

    case "date_mdy":
    $value = date_i18n( 'm/d/Y', forminator_local_timestamp(), true );

    And change it to this:

    case "date_mdy":
    $value = date_i18n( 'm/d/Y H:i:s', forminator_local_timestamp(), true );

    Please remember that all these changes will be lost with any plugin update and do check the release notes of new versions to see if this was fixed, else you will need to do it again.

    Hope to have helped and have a nice day.

    Best Regards,
    Jon

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.