Sample : Apps Script from an automation

[leinharte]

Hi!

I'm trying to call Apps Script from an automation, but it doesn't work for me.

I want to copy AppSheet's data to Google Sheet.

1. Google Sheet is like this:

ID :  1f3QY4Py34LC2**************************

2. Google Apps Script is like this :

function copyToRange(rangeFrom) {

    var spreadsheetTo = SpreadsheetApp.openById('1f3QY4Py34LC2**************************');

    var rangeTo = spreadsheetTo.getRange(1,1);

    rangeFrom.copyTo(rangeTo);

}

3. Automation is like this :

Could you please help me?

Thank you.

[WillowMobileSys]

Note that a Task is not the automation itself.  It is only a step that can be called from an automation.  Have you created a Bot with an Event and a Process that calls your newly created Task step?

[leinharte]

@WillowMobileSys  Yes, I did. There is an event that trigger the task.

I think that the event trigger the task properly.

But Apps Script seems to matter.

Thank you.

[WillowMobileSys]

You need to confirm if the script is actually getting called.  I would insert some test code into the script to allow you to confirm.  Maybe send yourself an email?

[leinharte]

Title : To copy "AppSheet Data" to "Google Sheet" by Apps Script

Like below image, I have copied the sentence column of AppSheet to sheet1 of Google Sheet(targetFile).

     <AppSheet Data : sentence column of SENTENCE table>

.          

<Google Sheet : sheet1 of targetFile>

Explanation

step1 : Apps Script

Apps Script project name is copyToRange and there are two functions(copyToRange_2, transpose)

- AppSheet task calls this function : "copyToRange_2"

- "transpose" function transpose the data.

Note: AppSheet supports only standalone scripts. It does not support container-bound scripts at this time.

function copyToRange_2(rangeFrom) {

    // targetFile ID : 1f3QY4Py34LC***************************

    var spreadsheetTo = SpreadsheetApp.openById('1f3QY4Py34LC***************************');

    // To transpose the data

    rangeFrom = transpose(rangeFrom);

    // rangeFrom.Length = 11

    var rangeTo = spreadsheetTo.getSheetByName('sheet1').getRange(1,1,11,1);  

    rangeTo.setValues(rangeFrom);

}

function transpose(array){

    var result = [];

    // Loop over array cols

    for (var col = 0; col < array[0].length; col++) {

    result[col] = [];

    // Loop over array rows

    for (var row = 0; row < array.length; row++) {

    // Rotate

    result[col][row] = array[row][col];

    }

    }

    return result;

}

step2 : Automation :

Make a bot and a task like below.

<bot>

<task>

 

※ For 2D array, I used List function : List(SENTENCE[sentence])

- THE END -

I hope it is helpful.