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.

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

• THE END -

I hope it is helpful.