CODEWITHSUNDEEP
google-sheetsgoogle-sheets-apizapier
Mel
Mel

Reputation: 21

Zapier to Google Sheets - Copy Down Formula

I am trying to automate a copy down formula function with existing zapier integration.

For new emails fitting certain criteria, zapier copies down email / message_original / date_time in columns a / b / c. In column D, I have a formula that I would like to automatically apply to all new rows - you can see row 18 column D is blank because it hasn't been applied yet.

I'm running into issues with google pushing my formula row down - I've tried, array formula, etc.. with no success.

Zapier Google Sheet:

ZAPIERGOOGLESHEET

Upvotes: 2

Views: 2920

Answers (4)

ChrisJ
ChrisJ

Reputation: 2802

I've got this working with arrayformula. The OP mentioned that this didn't work for them, but perhaps updates to Zapier or Google Sheets API's in the last 2 years have changed that. Zapier docs do mention that a zap from a sheet can take 3 minutes to fire, so that may give the formula enough time to copy down.

I tested this with a simple formula =arrayformula(B2:B&C2:C) and I can see that a Zap on a new entry always picks up the correct value from the arrayformula column

Upvotes: 0

Jean-Luc Vanhulst
Jean-Luc Vanhulst

Reputation: 11

Answering here since I was looking for a solution as well and couldn't really find one. I ended up creating a google script that copies all formulas of row from the previous row. (You can do that with getFormulasR1C1(), which gives you relative version) and wrapping that one as a callable doGet() so that I can use a zapier webhook as the next step. Deploy the App under Publish -> Publish -> deploy as web app. Make sure that the scope is EVERYONE because the Zapier web hook will run this.

function doGet(e) { // the zap will call here - use webhook and GET and make sure to set send as JSON to NO. 
                    //The paramater is called row so in query params in Zapier but "row" and in the value the row number you want to update 
                  
 var update = updateformulas( e.parameter.row); 
 var appData = {
 "heading": "Update",
 "DidUpdate": update  // tell the zap if we updated somethine
 };
 var JSONString = JSON.stringify(appData);
 var JSONOutput = ContentService.createTextOutput(JSONString);
 JSONOutput.setMimeType(ContentService.MimeType.JSON);
 return JSONOutput
}

function updateformulas(row,sheet = SpreadsheetApp.openById("<add target spreadsheet id>" ).getActiveSheet() )  {
var update = false;
var maxcols = sheet.getLastColumn();
if (row>0 ||maxcols>0) {
  var range = sheet.getRange(row-1,1,1,maxcols);
  var formulas = range.getFormulasR1C1(); // get all formulas from the PREVIOUS row
  for ( i=0; i<maxcols ;i++ ) {
    if (formulas[0][i]) { // if there was a formula in that column 
      sheet.getRange(row,i+1).setFormulaR1C1(formulas[0][i]); // set the formula for the current row
      update = true;
    }
  }
}
return update

Upvotes: 1

nhe
nhe

Reputation: 177

You can do this with a multi-step zap.

First step of your zap pulls data in from outside source.

Second step of the zap is to populate your Google sheet with the info coming form an outside source (like you're doing now).

Then add a third step: Google Sheets > Update Spreadsheet Row.

For Row, choose "Use a Custom Value (advanced)", and select the "ID" from the previous step.

Then for "message_updated", paste in your formula from Google Sheets. You just need to update any cell references to be dynamic. So for example if your formula references "B5" you need to change that to instead reference "B" and then use a Zapier reference to the "ID" from zap step 2. It will look kinda like this:

Relative cell reference in Zapier

You have to do it in two steps like this, because the row has to exist before you make a relative reference to it in Zapier.

Upvotes: 4

Diego
Diego

Reputation: 9571

You need to write your formula in Zapier and have Zapier paste it in along with the email, message_original, and date_time. See the pictured example:

enter image description here

Upvotes: 1

Related Questions