New to the community? Start here

[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

Fri, 28 Sep 2018 21:43:13
More Forminator Pro discussions
Forminator Pro
  • Jonjon
    • Site Builder, Child of Zeus

    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

    [attachments are only viewable by logged-in members]

    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 } );
    
}

    [attachments are only viewable by logged-in members]

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

    [attachments are only viewable by logged-in members]

    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
    • Site Builder, Child of Zeus

    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
    • Site Builder, Child of Zeus

    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});
    

    
}

    [attachments are only viewable by logged-in members]

    For me it works after page refresh.

    Hope to have helped.

    Best,

    Jon

  • Jonjon
    • Site Builder, Child of Zeus

    Hi Finca,

    I noticed you did not change your sheet to Sort variable. You have to modify this to match exactly your sheet name, mine is sheet 1 as you see in the screenshot below.

    [attachments are only viewable by logged-in members]

    If this does not solve your issue, please create a copy of your sheet without personal data I will take a quick look at it to see if I can help.

    Best Regards,

    Jon

  • Jonjon
    • Site Builder, Child of Zeus

    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
    • Site Builder, Child of Zeus

    Hi Finca.

    Actually yes, the trigger is the one that actually creates the date column value in a native format that the google engine recognizes so it can actually order it successfully. You could add that column after the last column of the Forminator form.

    Best Regards,

    Jon

  • Jonjon
    • Site Builder, Child of Zeus

    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:

    [attachments are only viewable by logged-in members]

    [attachments are only viewable by logged-in members]

    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:

    [attachments are only viewable by logged-in members]

    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