Add a row automatically - Google Sheet

Questions
1

Hello,

I just started to use n8n and it is amazing, thanks!

I have a Google Sheet scenario that I’m not able to do, maybe someone could help.

Here is the case:

guat
guat1384×158 66.8 KB

I would like a line to be automatically created above line 2 everyday, that the boxes A2, B2, C2, E2, F2 stay empty, but that the boxes D2 and G2 are filled with the same formulas as the line below.

Do you know if it’s possible to do these 2 actions?

Thanks a lot,
Julien

2

Hi @julien1, welcome to the community!

This isn’t something n8n can handle well I am afraid. The Google Sheets node only has an “append” operation inserting a new line at the bottom, not an insert above/insert below another row option.

You would essentially need to re-write the entire sheet to move rows around.

3

Hey @MutedJam

I found a workaround with google Apps Scripts to insert a new line with formulas:
function daily() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName(“N8N”);
sh.insertRowAfter(1);
sh.getRange(“D2”).setFormula(‘=C2/B2’);
}

But you’re right, I can’t append any data in the blank cases between 2 lines… Do you know how I could do this?

Thanks a lot,
Julien

1 Like
4

Hello, have you been able to find a solution to insert the new lines always above the others? I have the same situation …

5

Hello!
I used the following script on Google Sheet, and it works well.

function InsertLineGlobalApp() {
  var feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Global - app");
  
  // Vérifier si la feuille existe
  if (feuille) {
    // Ajouter une ligne au-dessus de la ligne 2
    feuille.insertRowBefore(2);
    
    // Placer la valeur "1" en cellule A2
    feuille.getRange("Y2").setValue("1");
  }
}

Then I ask n8n to fill the line that contains the value “1” in the column Y. The first one will be in Y2, so it will fill the line 2 :slight_smile:

6

Love the workaround.

You can also create a script that inserts a row and deploy it. The script can take your variables and use those variables for cell values in an inserted row.

Then in n8n use the HTTP node to call the script with cell content.

{“nodes”: [{“parameters”: {“method”: “POST”,“url”: “https://script.google.com/macros/s/AKqC4-_AqwKMHcXS7um61gP/exec”,“sendHeaders”: true,“headerParameters”: {“parameters”: [{“name”: “Content-Type”,“value”: “application/json”}]},“sendBody”: true,“bodyParameters”: {“parameters”: [{“name”: “actionType”,“value”: “Create”},{“name”: “title”,“value”: “={{ $json.Title }}”},{“name”: “content”,“value”: “={{ $json.Body.replace(/^\s*—\s*\r?\n/gm, ‘’).replace(/(^\s*(?:[±]|\d+[.)])\s.)\r?\n(?:[ \t]\r?\n)+(?=^\s(?:[±]|\d+[.)])\s)/gm, ‘$1\n’).replace(/\\([\\`_{}\\#+\-.!|>])/g, ‘$1’).replace(/\\(?=\s)/g, ‘’);  }}”},{“name”: “tempType”,“value”: “={{ $json.Template }}”}]},“options”: {}},“type”: “n8n-nodes-base.httpRequest”,“typeVersion”: 4.2,“position”: [144,-112],“id”: “beabd969-fd9c-4b0a-a81f-b31f65e611a1”,“name”: “HTTP Request”,“retryOnFail”: true,“waitBetweenTries”: 5000,“alwaysOutputData”: false}],“connections”: {“HTTP Request”: {“main”: []}},“pinData”: {},“meta”: {“templateCredsSetupCompleted”: true,“instanceId”: “2b2af1d320060dd21174bf49ad9a846f8842fbdc2355729bcc8d1ee957660c88”}}