This topic was automatically generated from Slack. You can find the original thread here.
Hi Pi, is it possible to add today’s day, today’s month, and today’s year using a function in specific Google Sheets columns using the Google Sheet add single row action, or do you have to define these values in a previous step and point to these value in the Google Sheet add single row action ?
Yes, it’s possible to add today’s day, month, and year directly in the Google Sheets “Add Single Row” action without defining these values in a previous step. You can use JavaScript’s built-in Date object to get these values. Here’s an example of how you can do it: export default defineComponent({ props: { google_sheets: { type: "app", app: "google_sheets", }, spreadsheetId: { type: "string", label: "Spreadsheet ID", description: "The ID of the Google Sheets spreadsheet", }, sheetId: { type: "string", label: "Sheet ID", description: "The ID of the sheet within the spreadsheet", }, }, async run({ steps, $ }) { const date = new Date(); const day = date.getDate(); const month = date.getMonth() + 1; // JavaScript months are 0-indexed. const year = date.getFullYear(); return await this.google_sheets.addSingleRow({ spreadsheetId: this.spreadsheetId, sheetId: this.sheetId, row: [day, month, year], }); }, }); In this example, the run method first creates a new Date object and extracts the day, month, and year. It then calls the addSingleRow method of the Google Sheets app, passing in the spreadsheet ID, sheet ID, and an array containing the day, month, and year as the row to be added.
Currently, selecting the Google Sheet add single row, I have an interface where I need to fill each Sheet column with a hardcoded value or to select a variable from a previous step. I cannot write code.
So I understand I have to create a new Node run custom code action, to write the code you send me, am I right ?
Yes, you’re correct. If you need to dynamically generate values (like today’s date) and use them in the Google Sheets “Add Single Row” action, you would need to create a new “Run Node.js code” step prior to the Google Sheets step. In this Node.js step, you can write the code to generate the values you need and export them. Then, you can reference these exported values in the Google Sheets action. Here’s a step-by-step guide: 1. Add a “Run Node.js code” action to your workflow. 2. In this action, write the code to generate today’s date. For example: const date = new Date(); const day = date.getDate(); const month = date.getMonth() + 1; // JavaScript months are 0-indexed. const year = date.getFullYear(); this.day = day; this.month = month; this.year = year; 3. After this step, add the Google Sheets “Add Single Row” action. 4. In the Google Sheets action, you can now reference the values exported from the previous step. For example, if you have columns for day, month, and year, you can fill them with steps.nodejs.day, steps.nodejs.month, and steps.nodejs.year, respectively. This way, you’re dynamically generating the values for day, month, and year in the Node.js step, and then using those values in the Google Sheets action.
in the first example you provided :
export default defineComponent({
props: {
google_sheets: {
type: "app",
app: "google_sheets",
},
spreadsheetId: {
type: "string",
label: "Spreadsheet ID",
description: "The ID of the Google Sheets spreadsheet",
},
sheetId: {
type: "string",
label: "Sheet ID",
description: "The ID of the sheet within the spreadsheet",
},
},
async run({ steps, $ }) {
const date = new Date();
const day = date.getDate();
const month = date.getMonth() + 1; // JavaScript months are 0-indexed.
const year = date.getFullYear();
return await this.google_sheets.addSingleRow({
spreadsheetId: this.spreadsheetId,
sheetId: this.sheetId,
row: [day, month, year],
});
},
});
in the first example you provided :
export default defineComponent({
props: {
google_sheets: {
type: "app",
app: "google_sheets",
},
spreadsheetId: {
type: "string",
label: "Spreadsheet ID",
description: "The ID of the Google Sheets spreadsheet",
},
sheetId: {
type: "string",
label: "Sheet ID",
description: "The ID of the sheet within the spreadsheet",
},
},
async run({ steps, $ }) {
const date = new Date();
const day = date.getDate();
const month = date.getMonth() + 1; // JavaScript months are 0-indexed.
const year = date.getFullYear();
return await this.google_sheets.addSingleRow({
spreadsheetId: this.spreadsheetId,
sheetId: this.sheetId,
row: [day, month, year],
});
},
});
when clicking on Spreadsheet ID, I do not have the list of Google Sheets element from my Drive. How to have the same behaviour as google sheets add single row action, when selecting “Spreadsheet” field, to have a list of all your Google Sheets document ?
Do you have the answer of my previous message ?
I apologize for the delay. To answer your question, currently, Pipedream’s Google Sheets component does not support dynamic dropdowns for Spreadsheet and Sheet IDs. However, you can manually enter the Spreadsheet ID and Sheet ID in the respective fields. If you want to create a custom component with dynamic dropdowns for Spreadsheet and Sheet IDs, you would need to use the async options method in the component’s props. This method is used to dynamically generate options for a prop based on an API call or other asynchronous operation. Here’s a simplified example of how you might implement this: export default defineComponent({ props: { google_sheets: { type: "app", app: "google_sheets", }, spreadsheetId: { type: "string", label: "Spreadsheet ID", async options({ google_sheets }) { // Call the Google Sheets API to list spreadsheets const spreadsheets = await google_sheets.listSpreadsheets(); // Return the spreadsheets as options return spreadsheets.map(spreadsheet => ({ label: spreadsheet.name, value: spreadsheet.id })); }, }, sheetId: { type: "string", label: "Sheet ID", async options({ google_sheets, spreadsheetId }) { // Call the Google Sheets API to list sheets within the selected spreadsheet const sheets = await google_sheets.listSheets(spreadsheetId); // Return the sheets as options return sheets.map(sheet => ({ label: sheet.name, value: sheet.id })); }, }, }, async run({ steps, $ }) { // Your code here }, }); Please note that this is a simplified example and the actual implementation might require additional code to handle API calls, error handling, etc. Also, the listSpreadsheets and listSheets methods used in this example are hypothetical and would need to be replaced with the actual methods to call the Google Sheets API.