Im trying to connect googlesheets to speckle… been going at it for hours but Im stuck.
Im using Apps scripts and I added a code that I generated with the help of chatgpt but I keep getting this error
:x: Error: Request failed for https://macad.speckle.xyz returned code 400. Truncated server response: {"errors":[{"message":"Unknown argument \"streamId\" on field \"Mutation.commitCreate\".","locations":[{"line":3,"column":20}],"extensions":{"code"... (use muteHttpExceptions option to examine full response)
Not really sure what Im doing since I dont know any coding but maybe you can take a look at this and tell me what Im doing wrong?? any tips?
// Speckle Connection Settings
var SPECKLE_SERVER = "https://macad.speckle.xyz";
var SPECKLE_TOKEN = "---------------------------------------------------"; // I removed the token for security here
var PROJECT_ID = "-------"; // HYPER-B project ID
var MODEL_ID = "--------"; // 00_data/program-calculator model ID
// Function to send data from Google Sheets to Speckle
function sendDataToSpeckle() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues(); // Get all data from the sheet
// Convert sheet data into structured Speckle Base object
var objects = [];
for (var i = 1; i < data.length; i++) { // Skip the first row (header)
objects.push({
name: data[i][0], // First column as name
value: data[i][1] // Second column as numerical value
});
}
// Step 1: Create an object in Speckle
var objectId = createSpeckleObject(objects);
if (!objectId) {
Logger.log(":x: Failed to create object in Speckle.");
return;
}
// Step 2: Commit the data to the model in Speckle
var commitId = createSpeckleCommit(objectId);
if (commitId) {
Logger.log(":white_check_mark: Data successfully committed to Speckle: " + commitId);
} else {
Logger.log(":x: Commit failed.");
}
}
// Function to create an object in Speckle
function createSpeckleObject(objectData) {
var query = `
mutation CreateObject($objectInput: ObjectCreateInput!) {
objectCreate(objectInput: $objectInput)
}
`;
var payload = {
query: query,
variables: {
objectInput: {
streamId: "28a211b286", // HYPER-B project ID
objects: [{
type: "Base",
data: objectData
}]
}
}
};
var response = makeSpeckleRequest(payload);
return response?.data?.objectCreate || null; // Return object ID
}
// Function to create a commit in Speckle
function createSpeckleCommit(objectId) {
var query = `
mutation CreateCommit($streamId: String!, $objectId: String!, $branchName: String!, $message: String!) {
commitCreate(streamId: $streamId, objectId: $objectId, branchName: $branchName, message: $message)
}
`;
var payload = {
query: query,
variables: {
streamId: "28a211b286", // Project HYPER-B
objectId: objectId,
branchName: "c0f5cc5917", // Model 00_data/program-calculator
message: "Updated data from Google Sheets"
}
};
var response = makeSpeckleRequest(payload);
return response?.data?.commitCreate || null; // Return commit ID
}
// Helper function to send requests to Speckle API
function makeSpeckleRequest(payload) {
var options = {
method: "post",
contentType: "application/json",
headers: {
Authorization: "Bearer " + SPECKLE_TOKEN
},
payload: JSON.stringify(payload)
};
try {
var response = UrlFetchApp.fetch(SPECKLE_SERVER + "/graphql", options);
return JSON.parse(response.getContentText());
} catch (e) {
Logger.log(":x: Error: " + e.message);
return null;
}
}
It looks like the error has to do with the query and not necessarily app script.
You should be able to test your GQL queries from the Explorer (after logging in), once you get them working you can move them to App script:
I managed to stream the data from googlesheets into Speckle with the following code:
// 🔹 Speckle Connection Settings
var SPECKLE_SERVER = "https://macad.speckle.xyz"; // Speckle account server
var SPECKLE_API_URL = SPECKLE_SERVER + "/graphql";
var SPECKLE_TOKEN = PropertiesService.getScriptProperties().getProperty("SPECKLE_TOKEN"); // Token stored in Script Properties
var STREAM_ID = "28a211b286"; // HYPER-B project ID
var MODEL_ID = "c0f5cc5917"; // 00_data/program-calculator model ID
// 🔹 Function to send data from Google Sheets to Speckle
function sendDataToSpeckle() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; // Get the first sheet
var data = sheet.getDataRange().getValues(); // Get all data
if (data.length < 2) {
Logger.log("❌ No data found in the first sheet.");
return;
}
// Convert sheet data into a structured Speckle Base object
var objectData = [];
var headers = data[0]; // First row as headers
for (var i = 1; i < data.length; i++) { // Skip headers
var row = {};
for (var j = 0; j < headers.length; j++) {
row[headers[j]] = data[i][j]; // Map values to headers
}
objectData.push(row);
}
Logger.log("📊 Structured Data: " + JSON.stringify(objectData, null, 2));
// Step 1: Create an object in Speckle
var objectId = createSpeckleObject(objectData);
if (!objectId) {
Logger.log("❌ Failed to create object in Speckle.");
return;
}
// Step 2: Commit the data to the model in Speckle
var commitId = createSpeckleCommit(objectId);
if (commitId) {
Logger.log("✅ Data successfully committed to Speckle: " + commitId);
} else {
Logger.log("❌ Commit failed.");
}
}
// 🔹 Function to create an object in Speckle
function createSpeckleObject(objectData) {
var query = `
mutation CreateObject($objectInput: ObjectCreateInput!) {
objectCreate(objectInput: $objectInput)
}
`;
var payload = {
query: query,
variables: {
objectInput: {
streamId: STREAM_ID,
objects: [{
speckle_type: "Base", // Explicitly mark as a Speckle Base object
name: "GoogleSheetsData",
totalChildrenCount: objectData.length,
data: {
entries: objectData // Store data inside "entries"
}
}]
}
}
};
var response = makeSpeckleRequest(payload);
return response?.data?.objectCreate || null; // Return object ID
}
// 🔹 Function to create a commit in Speckle
function createSpeckleCommit(objectId) {
if (Array.isArray(objectId)) {
objectId = objectId[0]; // Ensure objectId is a single string, not an array
}
var query = `
mutation CreateCommit($commit: CommitCreateInput!) {
commitCreate(commit: $commit)
}
`;
var payload = {
query: query,
variables: {
commit: {
streamId: STREAM_ID,
objectId: objectId,
branchName: "data/program-calculator/googlesheet-data",
message: "Updated data from Google Sheets"
}
}
};
var response = makeSpeckleRequest(payload);
return response?.data?.commitCreate || null; // Return commit ID
}
// 🔹 Helper function to send API requests to Speckle
function makeSpeckleRequest(payload) {
var options = {
method: "post",
contentType: "application/json",
headers: {
Authorization: "Bearer " + SPECKLE_TOKEN
},
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
var response = UrlFetchApp.fetch(SPECKLE_API_URL, options);
var jsonResponse = JSON.parse(response.getContentText());
if (response.getResponseCode() !== 200) {
Logger.log("❌ API Error: " + JSON.stringify(jsonResponse, null, 2));
return null;
}
return jsonResponse;
} catch (e) {
Logger.log("❌ Error: " + e.message);
return null;
}
}
I also checked with graphql explorer, and mi data looks like this:
My new problem is in the receiving end in grasshopper. The data is not parsed and I tried using a python code (chatgpt generated) and of course didn’t work. The data output I get is the following:
The Grasshopper connector is not designed to handle Dictionaries, so you can try updating your python code to loop through all the keys and values to output those instead.
Hey Nikos, I had help from Joao Silva, one of the instructors of my course. This is what he came up with:
import json
import ghpythonlib.treehelpers as th
def extract_speckle_entries(sheets_data):
“”"
Extract structured data from Speckle into a Grasshopper Data Tree.
Each entry will be on a separate branch.
“”"
# Step 1: Convert Speckle data into a dictionary properly
try:
sheets_dictionary = dict(list(sheets_data.items()))
except Exception as e:
return f"❌ Error: Failed to convert Speckle data to dictionary - {str(e)}"
# Step 2: Extract 'entries' safely
entries = sheets_dictionary.get("entries", [])
# Step 3: Convert entries into a structured data tree
structured_output = []
for entry in entries:
structured_output.append([
str(entry.get('Program', 'N/A')),
str(entry.get('Sector', 'N/A')),
str(entry.get('Total Area Per Person (m²)', 0)),
str(entry.get('Total Allocated Area (m²)', 0)),
])
# Step 4: Convert list into a Grasshopper Data Tree
return th.list_to_tree(structured_output)
INPUT: Speckle Receiver output
sheets_data = x # Connect the Speckle Receiver output in Grasshopper
