google sheets – How to use an expression or formula on values from IMPORTRANGE

I am trying to use an expression on the values that are imported from another spreadsheet. The IMPORTRANGE copies the raw data, and with QUERY I can then specify which columns to use, so I can skip some of them.

However, I want to process the imported values by some expression. The QUERY doesn’t seem to support different string functions like LEN, etc. But if I use an expression over the QUERY, like “=LEN(QUERY(…”, then I get only one record as a result, not a list of rows, as I would from IMPORTRANGE and QUERY.

Specifically, I have a source spreadsheet, that is filled automatically by one of my Google Forms, and there is a column with a list of personal names. This source spreadsheet is restricted to only a few users. But I have create another spreadsheet, for the public use. In this copy, I want to display all the non-sensitive columns, but for the personal names column, I want to display only a number of the names, which are comma-separated. The expression to get the number of the names is otherwise trivial:

=IF(LEN(TRIM(A1)) > 0; COUNTA(SPLIT(A1; ",")); "")

I could use the expression in the source spreadsheet, but it’s overwritten by Google Forms. So I need to use the expression in the copied spreadsheet.

What are the possible solutions? I haven’t found any questions similar to mine.

Average the last N values in a Sheets column using Arrayformula

Experts, I need your help…

I’m looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn’t.

Let me explain…

I have a list of values in column G of a google sheet. Once each week, a script adds a new row at the bottom of the sheet and a new value in column G of the new row. Each week, after the script runs, I need to get the average of the last N cells and paste the result in columns N, O,and P. (The average of 2 values pastes to column N, the average of 6 values pastes to column O, and the average of 13 values pastes to column P.)

Calculating an average of values in a column is simple.
Calculating the average of the last N values in a column is simple.
Calculating an average automatically for the last N values in a column is difficult. I don’t open the sheet every day, and even if I did, I usually don’t have the time (or desire) to update every time. I want this to be automatic. (If what I want to do is impossible with a formula, a script solution is acceptable.)

Here’s how I need it to work. The source values are in column G. (Row 1 is always the column label.) The values to be averaged start in cell G2 and continue to the end of column G.

I need to average G2 and G3, and paste the result in cell N3.
Then, average G3 and G4, and paste the result in cell N4.
Then, average G4 and G5, and paste the result in cell N5.
And so on, to the bottom of column G.

Then, I need to duplicate this, but with 6 values.
I need to average G2:G7, then paste the result in cell O7.
Then, average G3:G8, then paste the result in cell O8.
Then, average G4:G9, then paste the result in cell O9.
Again, all the way to the bottom of column G.

Finally, I need to do all of this again, only this time with 13 values.

(If someone can tell me how, I can share a sheet.)

Would anyone care to give me some insight?

json – postgresql error while using column values in jsonb_set function

I am trying to update a field value in jsonb column with text value present in another column but getting some syntax errors; not getting any solution.

i am trying to swap values of OutgoingVertexid & IncomingVertexId in below JSONB


so used below code by putting all values OutgoingVertexid & IncomingVertexid value in temp table.

UPDATE temp_table 
        owner_property = CASE 
                            WHEN owner_outgoing_edge IS NOT NULL 
                                THEN jsonb_set(owner_property, '{OutgoingVertexid}', '""')
                                jsonb_set(owner_property, '{OutgoingVertexid}', ''||owner_incoming_edge::jsonb||'') 

but getting below error:

ERROR: invalid input syntax for type json LINE 7: … jsonb_set(owner_property, ‘{OutgoingVertexid}’, ”””||ow… ^ DETAIL: Token “‘” is invalid.


Thanks in Advance

plugins – Update widget values from ajax function

I have widget like this

class Test extends WP_Widget
    public function __construct()
            __('Product recommender', 'password_domain'),
                'customize_selective_refresh' => true,

    // The widget form (for the backend )
    public function form($instance)
        // Set widget defaults
        $defaults = array(
            'email' => '',
            'password' => '',
            'limit' => '',
            'date' => ''

        // Parse current settings with defaults
        extract(wp_parse_args(( array )$instance, $defaults)); ?>

            <label for="<?php echo esc_attr($this->get_field_id('email')); ?>"><?php _e('Email', 'email_domain'); ?></label>
            <input class="widefat" id="<?php echo esc_attr($this->get_field_id('email')); ?>"
                   name="<?php echo esc_attr($this->get_field_name('email')); ?>" type="email"
                   value="<?php echo esc_attr($email); ?>"/>
            <label for="<?php echo esc_attr($this->get_field_id('password')); ?>"><?php _e('Password:', 'password_domain'); ?></label>
            <input class="widefat" id="<?php echo esc_attr($this->get_field_id('password')); ?>"
                   name="<?php echo esc_attr($this->get_field_name('password')); ?>" type="password"
                   value="<?php echo esc_attr($password); ?>"/>
        <p id="TEST"></p>
            <label for="<?php echo esc_attr($this->get_field_id('limit')); ?>"><?php _e('Limit', 'limit_domain'); ?></label>
            <input class="widefat" id="<?php echo esc_attr($this->get_field_id('limit')); ?>"
                   name="<?php echo esc_attr($this->get_field_name('limit')); ?>" type="number"
            <label for="<?php echo esc_attr($this->get_field_id('date')); ?>">Date updated: </label>
            echo esc_attr($date);
            <button onclick="ExportData()" type="button">Export database</button>


            function ExportData() {
                var data = {
                    'action': 'export_database', // the name of your PHP function!

                    type: "POST",
                    url: ajaxurl,
                    data: data,
                    dataType: 'json',
                    error: function (data) {
                        alertify.notify(data.errors, 'error', 5);

    <?php }

    public function update($new_instance, $old_instance)


    public function widget($args, $instance)


You will se that i have ajax function export_database that use to proceed some data. Is possible to update widget properties from this custom ajax function, not just submitting the form? Here is example of my ajax funcion

function export_database()

add_action('wp_ajax_export_database', 'export_database');  // for admins only
add_action('wp_ajax_nopriv_export_database', 'export_database'); // for ALL users

unity – Having multiple different friction values on a single piece of track?

I am trying to figure out a good/performant way to give a piece of track multiple values of dynamic friction. A track piece is 10×10 units in Unity and the idea is to puzzle them together to a big track like the tracks in Trackmania. The game itself features no acceleration, except from gravity. To make things more interesting I want to give each track piece at the start of the scene multiple random friction values within a given range.

Currently I achieve this by making each piece of track consisting of 100 little tiles which are 1×1 units and they get a random friction values assigned. This approach works somewhat fine as long as there are less than 30 to 40 track pieces(3000-4000 tiles) but with more than that the fps are dropping really low. As it is a racing type of game I had to set the “Fixed Timestep” in the project settings to 0.001 to get accurate time measurments and this is hurting the performance as well. With a lower timestep the collision detection with all the little tiles is really bad too.

Is there a more elegant and/or performant way to achieve this in within the unity physics system?

c++ – Assiging values on multiple clients in UE4

I am currently working on a multiplayer game and I am having difficulties on assigning values on variables for each clients. Originally I looped through available clients character from HUD then assigned the calculated the value but I decided to do it inside character blueprint’s instead.

I don’t know why only first clients gets the calculated values but the other client still having the default value.

This is the initial value that I assigned from inside Character C++

MaxHealth = 500;
Health = MaxHealth;

This is how I assigned the value from the Blueprint.

Set Initial Attributes is where I calculated the Health value based on player’s Strength value etc and the reason I’m doing this is because I am planning to retrieve the stats from the datatable in the future. But I don’t know why it becomes like this instead.

This is the correct final value that supposedly assigned on both clients.
First Client

But only first client gets the correct value, not second client.
Second Client

I don’t want to use loop to search the player characters’ inside player character’s blueprint and assigned the value because I don’t think it’s a wise move so I am looking for a better alternative if any. Thanks!

query – MySQL Dynamic Result view with column when values are on same column

I want to create a dynamic value based on election result. I’ve three table.

  1. vote table
  2. Candidate table
  3. Candidate position table.

Here the candidate table and position table is dynamic. its means I haven’t any control on it.

I want to make a view like this:
enter image description here

Here Ballton num come from vote table, president, general secretary and treasurer is come from candidate position table and against each ballot no all column will be populate.

Here are the rest of the table structure:

enter image description here

enter image description here

Candidate position:
enter image description here